Documentation Plugins Infinity data source Query UQL Parser

UQL Parser

UQL (Unstructured query language) is an advance query format in Infinity data source which will consolidate JSON, CSV, XML, GraphQL formats. UQL also provides ability to customize the results.

UQL is an opinionated query language designed for in-memory operations. UQL query can be formed with list of commands joined by |, in a line each. Most of the times, fields are referred within double quotes and string values are referred with single quotes. UQL was inspired by kusto query language and follows 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 four column table (id, dob, city, full name).

Basic UQL commands

All these commands are available in all versions unless specified otherwise.

project

project command is used to select the 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 fields.

sql Copy

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

project-away

project-away command is exactly opposite as project. It just drops specific columns from the data. It doesn’t support alias or dot notation selector.

sql Copy

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

order by

order by command sorts the input based on any column. sort direction should be either asc or desc

sql Copy

parse-json
| order by "full name" asc

extend

extend command is similar to project. but instead of selecting the columns, it just adds/replace columns in existing data. extends expects an alias and a function.

sql Copy

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

following are some of the available functions

For example, the data [ { "a": 12, "b" : 20 }, { "a" : 6, "b": 32} ] and the following 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")

wil produce 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") will apply tolower function and then trim function over the name field.

There are few other extend/project methods also available to deal with array

pack

pack method converts array of key value pairs into a map. Example extend "foo"=pack('key1',"value1",'key1',"value2") will yield a object {key1:value1,key2:value2}

array_from_entries

array_from_entries method builds an array of objects from entries. Example extend "foo"=array_from_entries('timestamp',[2010,2020,2030]) will yield 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 alias for keys instead of index. Example extend "foo"=array_to_map(['chennai','india'],'city','country') will yield { 'city': 'chennai', 'country':'india'}

summarize

summarize command aggregates the data by a string column. summarize command expects alias, summarize by fields and summarize function. Following are the valid summarize functions.

For example, 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 }
]

and the following 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

will produce the output table like this

pivot

pivot is the command used to perform pivot operations over the data. pivot command accepts 3 arguments.

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

Pivot example 1

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

and the following query

sql Copy

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

will produce

where as the following summarize query

sql Copy

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

will produce

so choose either summarize or pivot according to you needs

parse-json

parse-json is the command to instruct the UQL to parse the response as JSON

parse-csv

parse-csv is the command to instruct the UQL to parse the response as CSV

parse-xml

parse-xml is the command to instruct the UQL to parse the response as XML

parse-yaml

parse-yaml is used to specify that the results are in XML format

count

count gives the number of results.

sql Copy

parse-json
| count

limit

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

sql Copy

parse-json
| limit 10

scope

scope commands sets the context of the output data. It is useful when the results are insides nested json object.

example

json Copy

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

and the following uql query just results the “users” and ignores 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, the command mv-expand "user"="users" over following data

json Copy

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

will produce results like

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() command is used to convert the given object into key-value pairs.

Example: For the data { "a": {"name":"a1"}, "b": {"name":"b1"}, "c": {"name":"c1"} } and the query parse-json | project kv() will yield the following table

This command can be also used with arguments

Example: For the data { "data": { "a": {"name":"a1"}, "b": {"name":"b1"}, "c": {"name":"c1"} } } and the query parse-json | project kv("data") will yield the same results

project kv() command is available only from 0.8.7 of the plugin

JSONata

jsonata command accepts a JSONata query and apply over the previous input

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, jsonata command can be combined/piped with multiple commands. You can use JSONata for filtering the data as well.

JSONata support is available from 0.8.8 version

comments

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