Searching data / Building a query / Working with JSON objects in data tables

Working with JSON objects in data tables

When searching for data in a table that has columns 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:

To learn more about working with JSON, check out the articles about these operations:

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 column to a json datatype.
Jq evaluation (jqeval) - Uses a jq filter to extract values in a JSON object to a new column.
Jq filter compilation (jqcompile) - Formats text to be read by LINQ as a jq filter.

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

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

Check JSON content using the pretty-print view

In this section, you will learn to use the pretty-print view to format and extract values from your JSON content. You can start checking the following video to see the process through an example:

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 columns to show the data type icon, number of items and the level of depth.

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 column) will show all the content of the JSON syntax including the top level (level 0).

Extract a property value into a new column

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 columns in your table.

Check the box(es) of the desired node(s) or use the master checkbox at the top left of the window to select/unselect all. Then, click the Extract button at the top right of the window, which is enabled after checking at least one box.

The next window displays the selected nodes with the properties they will have when added as new columns so you can change them before confirming.
Give each column the desired name using the Column Name fields.
You can choose the data type for each new column or leave the JSON data type. The available options will be automatically determined for the kind of string recognized (for example, numbers could be integer or float). Click the data type icon of the desired row under the Type column to switch between the different options.

Click OK and the columns will be created in the table, each of them with the name and data type indicated and each cell displaying the corresponding value of the corresponding JSON.

This will apply to all the JSON in the same column as long as they present the exact same structure. For those that present a different structure, the cells of the newly created columns will display the value "null" and you would have to repeat the process to extract their values.

Performing operations in the search window

You can extract property values into new columns using the Jq evaluation (jqeval) and Jq filter compilation (jqcompile) operations. The new column 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.

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 column using LINQ, use the Jq evaluation (jqeval) operation to apply a jq filter to the JSON column. 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 column in string data type, we apply the To string (str) operation on the entire operation.

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

Download as PDF