Skip to content

Exploring JSON#

Datameer enables you to explore and understand your JSON expressions and extract the needed data. Any JSON source is supported, e.g. from Google Analytics or Hubspot.

JSON Representation#

When clicking on a JSON in the Data Grid, the associated representation dialog is shown:

  • the upper view section provides the fully formatted JSON data
  • the lower path section provides information about the path for each selected expression

You can extract and re-use the whole JSON string or only single lines. Clicking on the "Copy to Clipboard" icon copies the selection to the clipboard.

Working with JSON in the SQL Editor#

You can extract:

  • JSON attribute(s) that has/ have any predefined data type in the JSON expression without using a specific function
  • JSON object(s)
  • VARIANT data by using the TRY_PARSE_JSON function

Extracting Any JSON Attribute#

In the SQL Editor, you can extract any attribute from your JSON object easily.

To extract an attribute:

  1. Click on the "+" button from your data source and select "New SQL View". The SQL Editor opens.

  2. Remove the "*" in the editor and select the needed JSON below. The JSON exploring dialog opens.

  3. Select the line and click on the "Insert path expression" button. The expression is inserted in the query automatically.

  4. If needed, click on "Format" and then on "Run Query". The query proceeds. The preview is shown below.

  5. Click on "Apply". The new SQL node is shown in the Flow Area. The Preview is displayed in the Data Grid.

Extracting A JSON Object#

  1. Click on the "+" button from your data source and select "New SQL View". The SQL Editor opens.

  2. Remove the "*" in the editor and select the needed JSON below. The JSON exploring dialog opens.

  3. Select the object's lines and click on the "Insert path expression" button. The expression is inserted in the query automatically.

  4. If needed, click on "Format" and then on "Run Query". The query proceeds. The preview is shown below.

  5. Click on "Apply". The new SQL node is shown in the Flow Area. The Preview is displayed in the Data Grid.

Extracting VARIANT Data#

Datameer provides the TRY_PARSE_JSON conversion function in the SQL Editor to parse an input string as a JSON object, producing a VARIANT value.

To extract VARIANT data:

  1. Click on the "+" button from your data source and select "New SQL View". The SQL Editor opens.

  2. Remove the "*" in the editor, enter the needed query components like the source column and the SQL function 'TRY_TO_JSON' and select the needed JSON below. The JSON exploring dialog opens.

  3. Select the line and click on the "Insert path expression" button. The expression is inserted in the query automatically.

  4. If needed, click on "Format" and then on "Run Query". The query proceeds. The preview is shown below.

  5. Click on "Apply". The new SQL node is shown in the Flow Area. The Preview is displayed in the Data Grid.

Working with JSON in the Formula Builder#

You can extract JSON attributes in the Formula Builder with the function 'JSON_EXTRACT_PATH_TEXT(,'')' as well.

To extract JSON attributes:

  1. Click on the "+" button from your data source and select "Formula Builder". The Formula Builder opens.

  2. Add a new column. The new column appears.

  3. Enter the 'JSON_EXTRACT_PATH_TEXT' function and add both the column identifier and the path name from the JSON, and click on "Apply Formula". The formula proceeds. The preview is shown below.

    INFO: If you need to extract any other data type than STRING, you need to extend the function with the conversion functions, e.g. 'TRY_TO_DOUBLE(JSON_EXTRACT_PATH_TEXT( V , ‘city.coord.lat’ ))' to extract DOUBLE data.

  4. Confirm with "Apply". The Workbench opens and presents the extract in the Data Preview.