AirData Querying Capabilities

AirData Queries can perform basic CRUD operations on your AirData. Typically, this is done by creating Data Flows – specifically, ones that include AirData Request Data Operation – in the Connections Builder. The AirData Request Data Operation can also be run in the Connections Builder directly, either to test that a Query runs as intended or to perform a one-off interaction with your data.

There are a number of built-in methods available that allow you to interact with entries in the datastore such as QUERY, PUT, DELETE, INSERT, and PATCH. For more details, see the AirData Request Data Operation. Here, we focus here on the QUERY method and how you can use built-in filtering to hone in on your data.

For the following examples, let’s say we have a sample App Object in our Datastore called Dog. Each Dog entry has the following schema:

{
  "name": "Text",
  "Nicknames": "List<Text>",
  "breed": "Text",
  "age": "Integer",
  "owner": "Text",
  "isTrained": "Boolean"
}

The following is an example of an instance of a Dog:

{
  "name": "Goofy",
  "nicknames": [
    "goofy",
    "goofball"
  ],
  "breed": "pug",
  "age": 7,
  "owner": "Reyna",
  "isTrained": true
}

Let’s use this sample Datastore below with nine Dog entries and explore some of the filters available to query the Datastore.

These are the specifications of all the available per-field filters, grouped by categorical type of search. For AirData querying, the documentation format is used as such:

filterKeyWord: the primary name for the filter.

type: the Airkit data type of the field that you wish to query (text, list of texts, etc)

aliases: can be used to clarify the intent of or shorten your query using different filter key words.

For example, the following queries will operate identically:

{ "age": { "<": 2 } }

{ "age": { "lessThan": 2 } }

example query: A valid sample query based on the example datastore.

query returns: A description of what the sample query would return from example datastore.

String matching filters

[block:parameters] { "data": { "h-0": "Filter", "h-1": "Description", "h-2": "Aliases", "h-3": "Type", "h-4": "Example", "0-3": "T | Collection where T = type of field", "1-3": "T | Collection where T = type of field", "2-3": "T | Collection where T = type of field", "3-3": "T | Collection where T = type of field", "0-2": "=, in, any, eq ", "1-2": "~=, ieq, iEquals, insensitiveEquals, iAnyOf ", "2-2": "!=, not, none, neq, !eq ", "3-2": "like ", "0-1": "strict equality test", "1-1": "when T is Text, case-insensitive equality; when T is List, unordered equality; otherwise defaults to anyOf behavior", "2-1": "strict inequality test", "3-1": "string contains, only works on text fields", "0-0": "anyOf ", "1-0": "insensitiveAnyOf ", "2-0": "noneOf ", "3-0": "like ", "0-4": "\n{ \"breed\": \n { \"anyOf\": [\"mutt\" , \"pug\"] \n } \n}\n\nAll dogs exactly matching the string "mutt" or "pug" in the "breed" field.", "1-4": "{ \"name\": { \"insensitiveAnyOf\": [\"DAISY\" , \"PENNY\"] } }\n\nAll dogs with names matching the string "daisy" or "penny" regardless of capitalization.", "2-4": "{ \"owner\": { \"noneOf\": [\"Damon\", \"Jeff\"] } }\n\nAll dogs who have an owner that is NOT "Damon" or "Jeff".", "3-4": "{ \"name\": { \"like\": \"y\" } }\n\nAny dog that has the character "y" in their name.", "h-5": "Example output", "0-5": "", "1-5": "", "2-5": "", "3-5": "" }, "cols": 5, "rows": 4 } [/block]

Quantitative filters

[block:parameters] { "data": { "h-0": "Filter", "h-1": "Description", "h-2": "Alias", "h-3": "Type", "0-0": "lessThan", "h-4": "Example", "0-3": "T where T = type of field", "1-3": "T where T = type of field", "2-3": "T where T = type of field", "3-3": "T where T = type of field", "0-2": "<, lt", "1-2": "<=, lte", "2-2": ">, gt", "3-2": ">=, gte", "1-0": "lessThanOrEqual", "2-0": "greaterThan", "3-0": "greaterThanOrEqual", "0-1": "less than, exclusive", "1-1": "less than, inclusive", "2-1": "greater than, exclusive", "3-1": "greater than, inclusive", "0-4": "{ \"age\": { \"lessThan\": \"5\" } }\n\nAll dogs who are less than 5 years old.", "1-4": "{ \"age\": { \"lessThanOrEqual\": \"5\" } }\n\nAll dogs who are strictly younger than 5 years old.", "2-4": "{ \"age\": { \"greaterThan\": \"2\" } }\n\nAll dogs who are strictly older than 2 years.", "3-4": "{ \"age\": { \"greaterThanOrEqual\": \"10\" } }\n\nAll dogs who are at least 10 years old or older." }, "cols": 5, "rows": 4 } [/block]

List/set filters

[block:parameters] { "data": { "h-0": "Filter", "h-1": "Description", "h-2": "Alias", "h-3": "Type", "h-4": "Example", "0-0": "intersects", "1-0": "supersetOf", "2-0": "subsetOf", "3-0": "containsAnyOf", "4-0": "containsAllOf", "5-0": "containsNoneOf", "0-3": "Set where T = type of element type in List\n\n* field type must be List", "0-1": "matches where any of the elements in the filter also exist in the list", "0-2": "∩, intersect, intersection, intersectionOf", "0-4": "{ \"nicknames\": { \"intersects\": [ \"dog\", \"doggy\", \"Lulu\", \"doesn’t matter\" ] } }\n\nAny entry that has a nickname in the searched array. In our example, it returns entries 2,3,6 & 7.", "1-3": "Set | Set<Set> where T = type of element type in List\n\n* field type must be List", "1-2": "⊇, superset, overlap, overlaps, overlapsAny, supersetOfAny", "1-1": "matches where the field is a superset of any of the provided sets", "1-4": "{ \"nicknames\": { \"supersetOf\": [ \"doggy\", \"dog\" ] } }\n\nAny dog where the field is a superset of the array in the query. In our example, it’d only return entry 3.", "2-3": "Set | Set<Set> where T = type of element type in List\n\n* field type must be List", "2-2": "⊆, subset, overlapped, subsetOfAny, overlappedBy", "2-1": "matches where the field is a subset of any of the provided sets", "2-4": "{ \"nicknames\": { \"subsetOf\": [ \"pugsy\", \"doggy\", \"doodle\", \"dais\" ] } }\n\nAny dog whose nicknames are a subset of the list searched. In our example, it’s return entries 6,7, & 8", "3-3": "Set | Text where T = type of element type in List\n\n* field type must be List | Text", "3-2": "∈, containsAny", "3-1": "if field is list, matches if any element in this set exists in the field if text, matches if any of the strings are a substring of the field", "3-4": "{ \"nicknames\": { \"containsAnyOf\": [ \"dais\", \"harry\" ] } }\n\nAny dog who has any of the following nicknames. Our example will only return entries 8 & 9.\n{ \"nicknames\": { \"containsAnyOf\": \"pugsy\" } }\n\nIterates over the list and checks to see if query value is present as a substring in the list. Our example returns entries 4 & 9", "4-3": "Set | Text where T = type of element type in List\n\n* field type must be List | Text", "4-2": "containsAll", "4-1": "if field is list, matches if all elements in this set exist in the field if text, matches if all of the strings are a substring of the field", "4-4": "{ \"nicknames\": { \"containsAllOf\": [ \"doggy\", \"dog\" ] } }\n\nAny dog who has all of the following nicknames, but can have others as well. Our example only returns entry 3.", "5-3": "Set | Text where T = type of element type in List\t\n\n* field type must be List | Text", "5-2": "∉, containsNone", "5-1": "if field is list, matches if none of the elements in this set exist in the field if text, matches if none of the strings are a substring of the field", "5-4": "{ "nicknames": { "containsNoneOf": [ "doggy" ] } }\n\nAny dog who does not have the nickname "doggy". Our example returns 1,2,4,5,8, & 9." }, "cols": 5, "rows": 6 } [/block]

Sorting data

In order to sort data from Airdata, add a Transform Data Operation downstream of the AirData Request and use LINQ Syntax to specific how the data should be sorted.

For instance, if you are querying to get all dogs that are less than two years old and need to sort the results such that you have a list of the each dog's ages in descending order, you would use the following expression:

FROM item IN airData.results ORDER BY item.age DESCENDING SELECT item.age

Examples

You can use Airscript to further refine your search. For example:

{ "dogName": { "anyOf": "{{ LOWERCASE("LUCY")}}" } }

You can combine multiple queries to specify your search.

{ "dogName": { "insensitiveAnyOf": "daisy" }, "dogAge": { "greaterThan": 4 } }

📘 Note: Comma delimited queries function as an AND operation, not OR.