Knowledge Drop

Parsing JSON data in Looker

  • 6 April 2021
  • 0 replies

Userlevel 5

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.

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.                


0 replies

Be the first to reply!