Skip to main content
beck.smith1-Visitor
1-Visitor
November 7, 2015
Solved

How to populate query when testing the QueryStreamEntriesWithData service?

  • November 7, 2015
  • 5 replies
  • 4621 views

I found an almost two year old thread that asks the following question:

“I'm trying to test the QueryStreamEntriesWithData service by selecting the "Test" button beside the service.  One of the fields that needs to be populated is "query".  So if I want to filter the data such that I see only the stream entries that has one of the columns equal a specific value, what do I enter into this field? “

The reply at the time appears to be in the context of version 5.0 and was “Testing a Service which takes a query parameter is probably not something you want to attempt from Composer.” and it went on to describe an alternate method.  Here is the link to the original: How to populate the "query" field when testing Services that have a querying ability

Now, flash forward to today and my question:

Now that we are at version 6.x of the Composer has anything changed and thus is there a way to populate the query field when testing the QueryStreamEntriesWithData service as Loren originally requested?

I simply want to test filtering the query on Id = 1.  (Id is a numeric field in the Data Stream)

I consulted the Composer 6.0 Help for this exact issue and it does have an extensive description of how to construct a query string for all the query* test services (but alas no examples).  As a result, no matter what is placed in the "query" box of the QueryStreamEntriesWithData - Test Service in the Composer the error message is:


"Invalid QUERY value. Please make sure it's a valid JSON string"

Any guidance, especially with an example is greatly appreciated.

Thanks,

    Best answer by billrei

    I think I can answer this question for you. The JSON structure of a "Query" is:

    {"filters": {"type":"EQ","fieldName":"CustomerId","value": 2 } }

    This finds all the stream entries that have a CustomerId field = 2.

    {"filters":  {"type":"EQ","fieldName":"Firstname","value": "William" }}

    Here the fieldName values are coming from the DataShape of the stream.

    This is a like query for all Firstname beginning with the letter A

    {"filters":  {"type":"LIKE","fieldName":"Firstname","value": "A%" }}

    Here is a filter with two or conditions:

    {"filters": {"type": "Or","filters": [{"type": "EQ","fieldName": "CustomerId","value": 1 },{"type": "EQ","fieldName": "CustomerId","value": 2} ]}}

    Some of your type choices are EQ,LIKE,GE,GT,LE,LT.

    It is very important that you use quotes around all your property names like "value" or "type".

    Also, a badly formatted query is ignored and you get all the results. The only error you will see is if your JSON is not parseable  or if you don't have a "filters" property.

    To try these queries out Create a DataShape like this:

    ThingWorx_Composer 2.png

    And then use it to create a Stream. Populate that stream by using its AddStreamEntry() service. You only need to provide values for your DataShape fields.

    ThingWorx_Composer 3.png

    Then put in some field values:

    ThingWorx_Composer 4.png

    Then hit the save button.

    Now perform your queries with the QueryStreamEntriesWithData() service, only filling in the query field with examples like the ones I used above.

    5 replies

    billrei12-AmethystAnswer
    12-Amethyst
    November 7, 2015

    I think I can answer this question for you. The JSON structure of a "Query" is:

    {"filters": {"type":"EQ","fieldName":"CustomerId","value": 2 } }

    This finds all the stream entries that have a CustomerId field = 2.

    {"filters":  {"type":"EQ","fieldName":"Firstname","value": "William" }}

    Here the fieldName values are coming from the DataShape of the stream.

    This is a like query for all Firstname beginning with the letter A

    {"filters":  {"type":"LIKE","fieldName":"Firstname","value": "A%" }}

    Here is a filter with two or conditions:

    {"filters": {"type": "Or","filters": [{"type": "EQ","fieldName": "CustomerId","value": 1 },{"type": "EQ","fieldName": "CustomerId","value": 2} ]}}

    Some of your type choices are EQ,LIKE,GE,GT,LE,LT.

    It is very important that you use quotes around all your property names like "value" or "type".

    Also, a badly formatted query is ignored and you get all the results. The only error you will see is if your JSON is not parseable  or if you don't have a "filters" property.

    To try these queries out Create a DataShape like this:

    ThingWorx_Composer 2.png

    And then use it to create a Stream. Populate that stream by using its AddStreamEntry() service. You only need to provide values for your DataShape fields.

    ThingWorx_Composer 3.png

    Then put in some field values:

    ThingWorx_Composer 4.png

    Then hit the save button.

    Now perform your queries with the QueryStreamEntriesWithData() service, only filling in the query field with examples like the ones I used above.

      billrei12-Amethyst
      12-Amethyst
      November 7, 2015

      I also came across these additional query examples that might be useful. I have not tested them. Note that if you copy and paste them you are going to have to add quotes around each property name to get them to work as valid JSON.

      ----------------

      Below are examples of the filters for the query parameter.

      • Matches or NotMatches filter
        var query = { filters: { type: "Matches|NotMatches" fieldName: "Source", expression: "[Kettle,Filler]" } };
      • TaggedWith or NotTaggedWith filter
        var query = { filters: { type: "TaggedWith|NotTaggedWith", fieldName: "tags", tags: "Applications:Testing;Plants:Sedona" } };
      • Single Comparator When using LIKE/NOTLIKE you need to add your own Wild Cards (% or * or ? for a single character wild card). For example to find use LIKE Th%
        var query = { filters: { type: "GT, LT, GTE, LTE, NE, EQ, LIKE, NOTLIKE", fieldName: "Duration", value: "12" } };
      • In or Not In filter var jsonArray = [12,14];
        var query = { filters: { type: "IN, NOTIN", fieldName: "Duration", values: jsonArray } };
      • Between or NotBetween filter
        var query = { filters: { type: "Between, NotBetween", fieldName: "Duration", from: "2", to: "12" } };
      • MissingValue or NotMissingValue filter
        var query = { filters: { type: "MissingValue, NotMissingValue", fieldName: "OrderQuantity" } };
      • Near or NotNear filter
        var query = { filters: { type: "Near, NotNear", fieldName: "fieldName", distance: "50", units: "M(iles), K(ilometers), N(autical miles)" location: { latitude: "40.12", longitude: "51.24", elevation: "300", units: "WGS84" } } };
      • Composite Filter Options It is possible to combine multiple filters using either the And or Or filter type. The example below will filter for rows that have a duration greater than 12 and are tagged with a power outage maintenance issue in the tags field.
        var query = { filters: { type: "And", filters: [{ type: "GT", fieldName: "Duration", value: "12" },{ type: "TaggedWith", fieldName: "tags", tags: "MaintenanceIssues:PowerOutage" } ] } };
      Terms & ConditionsAccessibility statement