Working with JSON objects in data tables

[ 1 Check JSON content using the pretty-print view ] [ 2 Extract a property value into a new field ]

When searching for data in a table that has fields containing JSON syntax (JSON data type), the search window UI offers some useful features to make working with this information fast and easy. You can:

JSON limitations

You can apply query operations in the search window using json data. However, keep in mind that json type fields cannot yet be used to define aggregation tasks, or be added as grouping/aggregation keys when you group data.

In the meantime, you can use the functions described below to extract values from JSON objects and convert the new fields into other types using To Float (float), To Int (int), To boolean (bool), and To string (str) operations. Then you can use the new fields to create a new data table.

Check JSON content using the pretty-print view

You can open this view to check the content of a cell to better analyze it. Hover over any cell in your data table and click P on your keyboard to open this view in a separate window.

For JSON content, this is especially useful because it is displayed in a reader-friendly way: name/value pairs are shown on separate rows, values are color-coded by data type and there are fields to show the data type icon, number of items and the level of depth.

Cannot see the checkboxes?

To select fields, the message must be json data type. The message may be imported as string data type. To parse this string, use the to json operation.

You can copy the whole json using the Copy to clipboard button.

If the content of the JSON is too large to handle you can use the following options to help you through:

Click the expand/collapse arrows next to the nodes that contain subnodes to show the desired level of depth. You can quickly expand or collapse all of them by clicking the Expand all nodes/Collapse all nodes buttons at the top of the window, next to the search box.
Use the search at the top left of the window to help you locate a node by its name or property value using a string. The content will be filtered as you type, showing the number of results next to the search and highlighting the matches. Use the back/forward arrows inside the search to navigate through the results.
You can use the path selector at the bottom left of the window to filter by node and depth level:
- Clicking the data type icon or the three dots will open a selector for you to choose between the available nodes in the first level (level 1). Only the selected node and its content in the levels below, if any, will be shown.
- In case there is content below, the path selector will be updated with an additional icon and three dots for you to choose a node for the next level (level 2). Additional options will keep appearing in the path selector as you keep drilling down and until there is no further content in the level below. You can repeat the process until you reach the desired node and depth level.
- You can later change any of your selections by clicking on it and choosing another option. The path will be reconstructed accordingly, changing that level and removing the selections for the levels below. Clicking the name in blue at the beginning of the path (the name of the field) will show all the content of the JSON syntax including the top level (level 0).

Extract a property value into a new field

Using the pretty print view

The pretty-print view can be used with a JSON cell not only to check but also to extract information; you can put a specific property value into new fields in your table.

Performing operations in the search window

You can extract property values into new fields using the Jq evaluation (jqeval) operation. The new field is created in the JSON data type so if you want to use it in a subsequent operation, you will need to transform it into a different data type using the operations in the Conversion group. To apply the Jq evaluation operation, you have at your disposal other ancillary operations that provide you with anything you might need. Find below a summary of the whole group of operations:

Jq evaluation (jqeval) - Uses a jq filter to extract values in a JSON object to a new field.
Jq filter compilation (jqcompile) - Formats text to be read by LINQ as a jq filter.
Json value type (label) - Returns the data type of the value inside a JSON object.
To json (jsonparse) - Converts a field of string data type to json data type. If the json value has been uploaded or is from a parsed table, it is added as a string datatype by default. In order to use the pretty print and extract process, you must first convert the field to a json datatype.

Using LINQ syntax

If you want to perform the operations using LINQ syntax, you can stack the different operations into a single one to avoid unnecessary steps:

To extract a property's value into a new field using LINQ, use the Jq evaluation (jqeval) operation to apply a jq filter to the JSON field. Use the Jq filter compilation (jqcompile) operation to identify the jq filter as an argument.

select jqeval(jqcompile(".email"), json) as emailUser

In order to generate the new field in string data type, we apply the To string (str) operation on the entire operation.

select str(jqeval(jqcompile(".email"), json)) as emailUser

Related articles