Documentation Plugins Infinity data source Query UQL parser

UQL parser

UQL (Unstructured Query Language) is a query language for transforming and manipulating data from JSON, CSV, XML, and GraphQL sources. Inspired by the Kusto Query Language (KQL), UQL uses a pipeline syntax where commands are joined by |.

Syntax basics:

• Commands are separated by | (pipe), typically one per line
• Field names are enclosed in double quotes: "fieldName"
• String literals use single quotes: 'value'

Quick example

The following example demonstrates how UQL transforms nested JSON data into a flat table.

Input data:

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"
  }
]

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

Output:

Basic UQL commands

project

Select specific columns to include in the output. Use dot notation to access nested properties. To rename a column, use the syntax "new_name"="source_field".

SQL Copy

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

This selects id, the nested name.firstName field, and renames dob to date of birth.

project-away

Remove specific columns from the output. This command only accepts top-level field names — dot notation and aliases are not supported.

SQL Copy

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

order by

Sort results by a column in ascending (asc) or descending (desc) order.

SQL Copy

parse-json
| order by "full name" asc

where

Filter rows based on a condition. Only rows where the condition evaluates to true are included.

SQL Copy

parse-json
| where "age" > 18

Combine conditions with and or or:

SQL Copy

parse-json
| where "country" == 'USA' and "age" >= 21

Use in to match against multiple values:

SQL Copy

parse-json
| where "country" in ('USA', 'Canada', 'Mexico')

Use !in to exclude values:

SQL Copy

parse-json
| where "country" !in ('USA', 'Canada')

Supported operators: ==, !=, >, >=, <, <=, in, !in

extend

Add new columns or transform existing columns using functions. Use the syntax "column_name"=function("source_field").

SQL Copy

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

This converts dob to a datetime and transforms city to uppercase.

The following sections describe the functions available with extend, organized by category.

String functions

Type conversion functions

DateTime functions

Math functions

URL functions

Encoding functions

Math functions example

Input data:

JSON Copy

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

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")

Output:

Chain multiple transformations

To apply multiple transformations to the same field, repeat the field name in the extend command:

SQL Copy

parse-json
| extend "name"=tolower("name"), "name"=trim("name")

This applies tolower first, then trim to the name field.

Array functions

pack

Convert key-value pairs into an object.

SQL Copy

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

Output: {key1: value1, key2: value2}

array_from_entries

Build an array of objects from a list of values.

SQL Copy

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

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

array_to_map

Convert an array to an object with named keys.

SQL Copy

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

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

summarize

Aggregate data by grouping on one or more columns. Use the syntax "alias"=function("field") by "group_field".

Aggregation functions

Summarize example

Input 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 }
]

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

Output:

pivot

Transform rows into columns. The pivot command accepts three arguments:

1. Aggregation function — for example, count("id") or sum("salary")
2. Row field — the field to use for row grouping, for example, "country"
3. Column field — the field whose values become column headers, for example, "occupation"

Pivot example

Input data (CSV):

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

UQL query (pivot):

SQL Copy

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

Output:

Compare with summarize:

SQL Copy

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

Output:

Use pivot when you want values to become column headers. Use summarize when you want grouped rows.

Parser commands

These commands parse the raw response into a structured format:

count

Return the total number of rows.

SQL Copy

parse-json
| count

limit

Restrict the number of rows returned.

SQL Copy

parse-json
| limit 10

scope

Set the document root to a nested property. Useful when your data is wrapped in a container object.

Input data:

JSON Copy

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

UQL query:

SQL Copy

parse-json
| scope "users"

This returns only the users array, ignoring meta and count.

mv-expand

Expand multi-value arrays into separate rows. Use the syntax mv-expand "new_column"="array_column".

Input data:

JSON Copy

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

UQL query:

SQL Copy

parse-json
| mv-expand "user"="users"

Output:

JSON Copy

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

project kv()

Convert an object into key-value pair rows.

Input data:

JSON Copy

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

UQL query:

SQL Copy

parse-json
| project kv()

Output:

To access a nested object, pass the property name as an argument:

SQL Copy

parse-json
| project kv("data")

jsonata

Run a JSONata expression on the data. The JSONata query language provides powerful querying and transformation capabilities.

Basic example:

SQL Copy

parse-json
| jsonata "items[price > 100]"

Complex example with chained commands:

SQL Copy

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

The jsonata command can be combined with other UQL commands in a pipeline. Use it for filtering, transforming, or restructuring data.

Comments

Lines starting with # are treated as comments and ignored during execution.

SQL Copy

parse-json
# Filter to active users only
| where "status" == 'active'
# Sort by name
| order by "name" asc