Working with JSON objects in data tables
When searching for data in a table that has columns containing JSON syntax, the search window UI offers a couple of useful features to make working with this information fast and easy. You can:
Open the pretty-print view
Hover over any cell in your data table and click P on your keyboard to open a window displaying the cell's contents.
For JSON content, this is especially useful. If the content of the cell is of json data type (link), the window displays it in a reader-friendly way: name/value pairs are shown on separate lines and values are color-coded by data type. Like this:
Extract a property's value into a new column
When your data table has a field that contains a JSON object, you may want to extract specific property values into new columns in your query. This is quickly done using the pretty-print view. Just click on a name/value row and the Operations Over Columns window opens to the Create Column tab. The Jq evaluation (jqeval) operation is used with the selected property value and column name as arguments. All you need to do is enter an appropriate Column Name and click Create Column.
The new column is created with the column values in the json data type. However, if you want to use the new column data in a subsequent operation, you will need it in a different data type, such as string or integer. Using the example of the email, we would probably want this as a string. To do this, you can use the To string (str) create column operation.
Working in LINQ
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 str operation on the entire Jq evaluation (jqeval) operation.
select str(jqeval(jqcompile(".email"), json)) as emailUser