Documentation Plugins Infinity data source Query UQL Parser

UQL Parser

UQL (Unstructured Query Language) is an advanced query format in the Infinity data source which consolidates JSON, CSV, XML and GraphQL formats. UQL also provides the ability to customize results.

UQL is an opinionated query language designed for in-memory operations. A UQL query consists of a list of commands joined by |, with each command on its own line. Most of the time, fields are referred to within double quotes, with single quotes used for string literals. UQL was inspired by the Kusto query language and uses similar syntax.

If your data looks like this:

JSON Copy

[
  {
    "id": 1,
    "name": { "firstName": "john", "lastName": "doe" },
    "dob": "1985-01-01",
    "city": "chennai"
  },
  {
    "id": 2,
    "name": { "firstName": "alice", "lastName": "bob" },
    "dob": "1990-12-31",
    "city": "london"
  }
]

then the following UQL query:

SQL Copy

parse-json
| extend "full name"=strcat("name.firstName",' ',"name.lastName"), "dob"=todatetime("dob")
| project-away "name"
| order by "full name" asc

will produce a four column table (id, dob, city, full name).

Basic UQL commands

The following commands are available in all versions of the plugin unless specified otherwise.

project

project is used to select columns to include in the results. If you want to select a property inside a nested object, you can use dot notation. Optionally, you can also alias the field names.

SQL Copy

parse-json
| project "id", "name.firstName", "date of birth"="dob"

project-away

project-away is the opposite of project. It drops specific columns from the data. It doesn’t support aliases or the dot notation selector.

SQL Copy

parse-json
| project-away "id", "city"

order by

order by sorts the input based on any column. The sort order can be specified and should be either asc (ascending) or desc (descending).

SQL Copy

parse-json
| order by "full name" asc

extend

extend is similar to project but instead of selecting columns, it adds or replaces columns in existing data. extend expects an alias and a function.

SQL Copy

parse-json
| extend "dob"=todatetime("dob"), "city"=toupper("city")

Functions that can be used with extend are described in the table below.

For example, running this UQL query:

SQL Copy

parse-json
| project "a", "triple"=sum("a","a","a"),"thrice"=mul("a",3), sum("a","b"),  diff("a","b"), mul("a","b")

over the data

[ { "a": 12, "b" : 20 }, { "a" : 6, "b": 32} ]

yields the following output:

To apply multiple transformations over a field, repeat them with the same field name. For example, the UQL query:

extend "name"=tolower("name"), "name"=trim("name")

applies the tolower function and then the trim function over the name field.

When working with data in arrays, there are a few other extend/project functions available as follows.

pack

pack converts an array of key value pairs into a map. Example:

extend "foo"=pack('key1',"value1",'key1',"value2")

yields an object:

{key1:value1,key2:value2}

array_from_entries

array_from_entries builds an array of objects from an array. Example:

extend "foo"=array_from_entries('timestamp',[2010,2020,2030])

yields an array:

[{timestamp:2010},{timestamp:2020},{timestamp:2030}]

array_to_map

array_to_map converts an array of entries to a map. Optionally, one can provide aliases for keys instead of their index. Example:

extend "foo"=array_to_map(['chennai','india'],'city','country')

yields

{ 'city': 'chennai', 'country':'india'}

summarize

summarize aggregates the data by a string column. summarize expects alias, summarize by fields and summarize function arguments. The available summarization functions are listed in the table.

For example, given the following data:

JSON Copy

[
  { "city": "tokyo", "country": "japan", "population": 200 },
  { "city": "newyork", "country": "usa", "population": 60 },
  { "city": "oslo", "country": "usa", "population": 40 },
  { "city": "new delhi", "country": "india", "population": 180 },
  { "city": "mumbai", "country": "india", "population": 150 }
]

Running this UQL query

SQL Copy

parse-json
| summarize "number of cities"=count(), "total population"=sum("population") by "country"
| extend "country"=toupper("country")
| order by "total population" desc

produces output like this:

pivot

The pivot command performs pivot operations over the data. It accepts 3 arguments.

• The 1st argument is the summarization function. Example: count("id) or sum("salary")
• The 2nd argument is the row field name. Example: "country"
• The 3rd argument is the column field name. Example: "occupation"

Pivot example 1

Given this CSV data:

csv Copy

name,age,country,occupation,salary
Leanne Graham,38,USA,Devops Engineer,3000
Ervin Howell,27,USA,Software Engineer,2300
Clementine Bauch,17,Canada,Student,
Patricia Lebsack,42,UK,Software Engineer,2800
Leanne Bell,38,USA,Senior Software Engineer,4000
Chelsey Dietrich,32,USA,Software Engineer,3500

Running the following query:

SQL Copy

parse-csv
| extend "salary"=tonumber("salary")
| pivot sum("salary"), "country", "occupation"

produces this output:

Whereas the following summarize query:

SQL Copy

parse-csv
| extend "salary"=tonumber("salary")
| summarize "salary"=sum("salary") by "country", "occupation"

will produce:

Choose either summarize or pivot according to your needs.

parse-json

parse-json parses the response as JSON.

parse-csv

parse-csv parses the response as CSV.

parse-xml

parse-xml parses the response as XML.

parse-yaml

parse-yaml parses the response as YAML.

count

count returns the number of results.

SQL Copy

parse-json
| count

limit

limit restricts the number of results returned. For example, the following query returns only the first 10 results:

SQL Copy

parse-json
| limit 10

scope

scope sets the context (document root) of the output data. It is useful when the results are inside a nested JSON object.

example

JSON Copy

{
  "meta": { "last-updated": "2021-08-09" },
  "count": 2,
  "users": [{ "name": "foo" }, { "name": "bar" }]
}

and the following UQL query returns only the contents of the “users” key, ignoring the other root level properties.

SQL Copy

parse-json
| scope "users"

mv-expand

mv-expand expands multi-value properties into their own records. For example, running the command mv-expand "user"="users" over this data:

JSON Copy

[
  { "group": "A", "users": ["user a1", "user a2"] },
  { "group": "B", "users": ["user b1"] }
]

Produces results like this:

JSON Copy

[
  { "group": "A", "user": "user a1" },
  { "group": "A", "user": "user a2" },
  { "group": "B", "user": "user b1" }
]

mv-expand should also work for non-string arrays.

project kv()

project kv() is used to convert the given object into key-value pairs.

Example, given the data:

{ "a": {"name":"a1"}, "b": {"name":"b1"}, "c": {"name":"c1"} }

and the query

parse-json | project kv()

the following table is returned:

This command can be also used with arguments. For example, given the data

{ "data": { "a": {"name":"a1"}, "b": {"name":"b1"}, "c": {"name":"c1"} } }

and the query

parse-json | project kv("data")

the same results as the previous example are returned.

project kv() is available from version 0.8.7 of the plugin

JSONata

jsonata accepts a JSONata query to run over the output of the previous command.

SQL Copy

parse-json
| scope "library"
| jsonata "library.loans@$L.books@$B[$L.isbn=$B.isbn].customers[$L.customer=id].{ 'customer': name, 'book': $B.title, 'due': $L.return}"
| count

Like any other command, the jsonata command can be combined/piped with multiple commands. You can use JSONata for filtering the data as well.

JSONata support is available from version 0.8.8 of the plugin

Comments

Any new line that starts with # will be treated as a comment.