Jq evaluation (jqeval)

[ 1 Description ] [ 2 How does it work in the search window? ] [ 3 How does it work in LINQ? ]

Description

Uses the jq json processor to identify and extract a single value from a json object into another column. This operation relies on the Jq filter compilation (jqcompile) and To json (jsonparse) operations.

How does it work in the search window?

Select Create column in the search window toolbar, then select the Jq evaluation operation. You need to specify two argument:

Argument	Data type	Description

Argument

Data type

Description

jq (mandatory)

jq

The filter to locate the part of the json syntax from which to extract the data. You can manually enter the string or select a column with jq data type.

For a successful extraction, the jq filter specified must coincide with the name of an item inside the intended JSON and must start with a dot operator (.) – jq filters always start with a dot.

json (mandatory)

json

The full sample with json syntax from which to extract the data. You can manually enter the string or select a column with json data type.

For a successful extraction, it must contain valid json syntax.

The data type of the values in the new column is json. However, if you need to transform the data type of the new column to use it somewhere else, you can use the operations in the Conversion group (for example, the To string (str) operation to obtain a column with string data type).

Alternatively:

You can use the json editor of the pretty-print view to perform this operation automatically, obtain several columns at the same time and transform the data types in the same step. To know more, check the article Working with JSON objects in data tables.

Example

In this example, we will perform the To json (jsonparse) operation to generate a json column with the following syntax:

{"name":"John","birth":"1986-12-14","city":"New York"}

Then, we will extract the city value from the json column:

Click the Create Column button on the toolbar, select the Jq evaluation (jqeval) operation and give the new column a name.
Add the necessary arguments and configure them as shown in the picture below:
- jq → manually introduced → .city
- json → column selected → Json
Click Create Column and a new column will be created with the city value for each event's json object.

How does it work in LINQ?

When using the search window interface to manually introduce the content of the jq argument, the Jq filter compilation (jqcompile) operation will be transparently performed to convert the string specified into a jq filter in jq data type. This means that you will not notice that such an intermediate step exists. This also applies to the json argument; when it is manually introduced, the To json (jsonparse) operation is performed in the background to convert the string specified into json data type.

Though this conversion is implicit while using the search window interface, it must be explicitly done when writing the query in LINQ.

Use the operator select... as... and add the operation syntax to create the new column. This is the syntax for the Jq evaluation operation:

jqeval(jq, json)
jqeval(jqcompile(.string), json)
jqeval(jqcompile(.string), jsonparse(string))
jqeval(jq, jsonparse(string))

Example

You can use the following LINQ script to recreate the example above in any table (for example, in demo.ecommerce.data):

from demo.ecommerce.data
select jsonparse("{ \"name\":\"John\", \"birth\":\"1986-12-14\", \"city\":\"New York\"}") as Json
select jqeval(jqcompile(".city"), Json) as City

If you want to extract the value as a string data type, you can apply the syntax to the entire Jq evaluation operation:

from demo.ecommerce.data
select jsonparse("{ \"name\":\"John\", \"birth\":\"1986-12-14\", \"city\":\"New York\"}") as Json
select str(jqeval(jqcompile(".city"), Json)) as City