UQL


UQL (Unstructured query language) is advance query format in infinity datasource 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.

UQL is still in beta but used widely. If you encounter any issues with uql, create a bug here.

if your data looks like this,

[
  {
    "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

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#

following are the basic UQL commands. All these commands are available in all the version unless specified.

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.

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.

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

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.

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

following are some of the available functions

function keywordsyntaxdescriptionavailable from
trimtrim("name")trims the string0.8.0
trim_starttrim_start("name")removes the space before0.8.0
trim_endtrim_end("name")removes the space after0.8.0
tonumbertonumber("age")converts a string into number0.8.0
tostringtostring("age")converts a number into string0.8.0
todatetimetodatetime("age")converts a datetime string into datetime0.8.0
unixtime_seconds_todatetimeunixtime_seconds_todatetime("dob")converts unix epoch s timestamp to datetime0.8.0
unixtime_nanoseconds_todatetimeunixtime_nanoseconds_todatetime("dob")converts unix epoch ns timestamp to datetime0.8.0
unixtime_milliseconds_todatetimeunixtime_milliseconds_todatetime("dob")converts unix epoch ms timestamp to datetime0.8.0
unixtime_microseconds_todatetimeunixtime_microseconds_todatetime("dob")converts unix epoch microsecond timestamp to datetime0.8.0
format_datetimeformat_datetime("dob",'DD/MM/YYYY')converts datetime to a specific format0.8.0
add_datetimeadd_datetime("dob",'-1d')adds duration to a datetime field0.8.0
startofminutestartofminute("dob")rounds the datetime field to the starting minute0.8.0
startofhourstartofhour("dob")rounds the datetime field to the starting hour0.8.0
startofdaystartofday("dob")rounds the datetime field to the starting day0.8.0
startofmonthstartofmonth("dob")rounds the datetime field to the starting month0.8.0
startofweekstartofweek("dob")rounds the datetime field to the starting week0.8.0
startofyearstartofyear("dob")rounds the datetime field to the starting year0.8.0
extractextract('regex',index,"col1")extracts part of the string field using regex and match index (0/1/..)1.0.0
sumsum("col1","col2")sum of two or more columns0.8.0
diffdiff("col1","col2")difference between two columns0.8.0
mulmul("col1","col2")multiplication of two columns0.8.0
divdiv("col1","col2")division of two columns (col1/col2)0.8.0
percentagepercentage("col1","col2")percentage of two columns ((col1/col2)*100)1.0.0
strcatstrcat("col1","col2")concatenates two or more columns0.8.0
splitsplit("col1",'delimiter')splits a string using delimiter1.0.0
replace_stringreplace_string("col1",'src','replacer')replace a portion of string with another1.0.0
reverserevers("col1")reverse a string1.0.0
floorfloor("col1")calculates the floor value of given numeric field0.8.7
ceilceil("col1")calculates the ceil value of given numeric field0.8.7
roundround("col1")calculates the round value of given numeric field0.8.7
signsign("col1")calculates the sign value of given numeric field0.8.7
powpow("col1",3)calculates the pow value of given numeric field0.8.7
sinsin("col1")calculates the sin value of given numeric field0.8.7
coscos("col1")calculates the cos value of given numeric field0.8.7
tantan("col1")calculates the tan value of given numeric field0.8.7
loglog("col1")calculates the log value of given numeric field0.8.7
log2log2("col1")calculates the log2 value of given numeric field0.8.7
log10log10("col1")calculates the log10 value of given numeric field0.8.7
parse_urlparse_url("col1")parses the col1 as URL0.8.6
parse_url("col1",'pathname')returns the pathname of the URL. Options are host,hash,origin,href,protocol and search0.8.6
parse_url("col1",'search','key1')returns the query string value for key1. 2nd arg is always search0.8.6
atobatob("col1")returns atob value of a string column. reference1.3.0
btoabtoa("col1")returns btoa value of a string column. reference1.3.0
substringsubstring("col1",1,5)returns substring value of a string column. reference1.3.0

For example, the data [ { "a": 12, "b" : 20 }, { "a" : 6, "b": 32} ] and the following uql query

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

atriplethricesumdiffmul
12363632-8240
6181838-26192

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.

function keywordsyntaxdescriptionavailable from
countcount()count of values0.8.0
sumsum("age")sum of age0.8.0
minmin("population")min of population0.8.0
maxmax("foo")max of foo0.8.0
meanmean("foo")mean of foo0.8.0

For example, the following data

[
  { "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

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

countrynumber of citiestotal population
INDIA2330
JAPAN1200
USA2100

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#

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

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

will produce

countryDevops EngineerSoftware EngineerStudentSenior Software Engineer
USA3000580004000
CANADA0000
UK0280000

where as the following summarize query

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

will produce

countryoccupationsalary
USADevops Engineer3000
USASoftware Engineer5800
CanadaStudent0
UKSoftware Engineer2800
UKSenior Software Engineer4000

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.

parse-json
| count

limit#

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

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

{
  "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.

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

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

will produce results like

[
  { "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

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

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

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 comment.