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 field. This operation relies on the Jq filter compilation (jqcompile) and To json (jsonparse) operations.

Alternatively, you can use the json editor of the pretty-print view to perform this operation automatically, obtain several fields 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.

How does it work in the search window?

Select Create field 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 field 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 field with json data type. For a successful extraction, it must contain valid json syntax.

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

Example

In this example, in the demo.ecommerce.data table, we will perform the To json (jsonparse) operation to generate a json field with the following syntax:

select jsonparse ({"name":"John","birth":"1986-12-14","city":"New York"}= as …

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

Click the Create field button on the toolbar, select the Jq evaluation (jqeval) operation and give the new field a name.
Add the necessary arguments and configure them as shown in the picture below:
- jq → select the pencil icon and write → .city
- json → field selected → Json
Click Create field and a new field 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 field. 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