FLATTEN YOUR JSONs!

Get out of the curly braces and embrace the relation(al)

Every Data Engineers/Analysts should know about SQL JSON functions!

It's common in data workflows to ingest data from API's. The API responses are stored in a data lake/warehouse as JSON. Until that JSON is transformed, the data is mostly useless.

JSON data is represented as key-value pairs, similar to a dictionary in Python.

SQL JSON functions let you parse JSON to extract the data inside. They allow you to turn semi-structured JSON data into tabular data so you can work with it in SQL.

Here are two SQL JSON functions you should know:

• JSON_EXTRACT() : can return both scalar and non-scalar values, including entire JSON objects or arrays

  • Use JSON_EXTRACT_SCALAR() instead of JSON_EXTRACT() if you want to remove double quotes from the result.

• JSON_VALUE(): extracts a single scalar value (like a string, number, or boolean) from JSON data and returns it as a string.

Examples of both:

Notice the difference between JSON_EXTRACT() and JSON_VALUE() ?