How to populate query when testing the QueryStreamEntriesWithData service?

[beck.smith]

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,

[billrei · Accepted solution]

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:

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.

Then put in some field values:

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.

[billrei]

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" } ] } };

[beck.smith]

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!

[billrei]

I have been in your shoes trying to make queries and having no idea what the structure was. A few examples go a long way.

[beck.smith]

CORRECTION to my example reply it should read:

{"filters": {"type":"EQ","fieldName":"Id","value": 4 }    <-- Wrong: missing a closing }

{"filters": {"type":"EQ","fieldName":"Id","value": 4 }}  <-- Right: Note the extra closing } that was not in my initial reply.

Didn't want to misdirect anyone who may need this thread for assistance, so I felt strongly the need to post this correction.

The syntax is ultra-tedious (IMHO) and I just have to get used to it. :-)

Again, thanks a ton to Bill for the examples.

I agree, a few examples go a long way!   i.e. "Teach a man to fish..."

Thanks,