5 Replies Latest reply on Nov 26, 2015 5:33 AM by bsmith RSS
    bsmith Apprentice

    How to populate query when testing the QueryStreamEntriesWithData service?

    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,

      • Re: How to populate query when testing the QueryStreamEntriesWithData service?
        billrei Creator

        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.

          • Re: How to populate query when testing the QueryStreamEntriesWithData service?
            billrei Creator

            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" } ] } };
            • Re: How to populate query when testing the QueryStreamEntriesWithData service?
              bsmith Apprentice

              Thanks Bill,

               

              Using your examples, I was able to format the JSON syntax correctly and successfully execute a query to manage a result set: 

               

              {"filters": {"type":"EQ","fieldName":"Id","value": 4 }

               

              Now that I have an example of the correct syntax, I will explore the many options and links you provided with the goal to hopefully create more complex and compound queries that address requirements of my project.

               

              Thanks again for the prompt and informative reply!