Parsing JSON data in Looker

[LookerExperts]

Knowledge Drop

Last Tested: Apr 3, 2019

Looker doesn't have a native JSON field type.

Workaround 1: SQL Operators

You'll want to use JSON parsing functions in SQL, like json_extract_path in Postgres and JSON_EXTRACT in BigQuery to extract the JSON and put it into a type that Looker can accept, like a string.

There are a few Community articles where we have examples of doing this.

https://community.looker.com/lookml-5/defining-json-objects-as-lookml-dimensions-2356

https://community.looker.com/sql-10/using-postgres-and-json-functions-and-operators-to-parse-json-fi...

NOTE: in BigQuery a JSON path must start with a $ followed by the index position and the string to parse, like: JSON_EXTRACT(${TABLE}.temperature_alerts, '$[0].description')

Workaround 2: API Parsing

Here is a cool open-source Python script which uses the Looker API to automatically detect JSON fields in the underlying database table and generates LookML for them (this is specific to Snowflake connections and may require adjusting to fit your use case).

This content is subject to limited support.