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 QUERYPUTDELETEINSERT, 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.

844844

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.os-querying-1.pngos-querying-1.png

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
720720

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.


Did this page help you?