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

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

Quantitative filters

Filter	Description	Alias	Type	Example
lessThan	less than, exclusive	<, lt	T where T = type of field	`{ "age": { "lessThan": "5" } }` All dogs who are less than 5 years old.
lessThanOrEqual	less than, inclusive	<=, lte	T where T = type of field	`{ "age": { "lessThanOrEqual": "5" } }` All dogs who are strictly younger than 5 years old.
greaterThan	greater than, exclusive	>, gt	T where T = type of field	`{ "age": { "greaterThan": "2" } }` All dogs who are strictly older than 2 years.
greaterThanOrEqual	greater than, inclusive	>=, gte	T where T = type of field	`{ "age": { "greaterThanOrEqual": "10" } }` All dogs who are at least 10 years old or older.

List/set filters

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

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.