Someone dumped JSON into your database! {“uh”: “oh”, “anything”: “but json”}. What do you do?

Relational databases are beginning to support document types like JSON. It’s an easy, flexible data type to create but can be painful to query.

Here’s how to extract values from nested JSON in SQL 🔨:

Example

{"userId": "555", "transaction": {"id": "222", "sku": "ABC"}

Let’s select a column for each userId, id, and sku. The following queries return 555, 222, ABC.

Postgres

Use the ->> operator to extract a value as text, and the -> to extract a JSON object:

select
    my_json_field ->> 'userId',
    my_json_field -> 'transaction' ->> 'id',
    my_json_field -> 'transaction' ->> 'sku'
from my_table;

Redshift

Use the json_extract_path_text function:

select
    json_extract_path_text(my_json_field, 'userId'),
    json_extract_path_text(my_json_field, 'transaction', 'id'),
    json_extract_path_text(my_json_field, 'transaction', 'sku')
from my_table;

MySQL

MySQL 5.7 includes JSON support. Hurray! Use the json_extract function:

select
    json_extract(my_json_field, '$.userId'),
    json_extract(my_json_field, '$.transaction.id'),
    json_extract(my_json_field, '$.transaction.sku')
from my_table;

Learn more

• Querying JSON in Postgres
• JSON Docs: Postgres, Redshift, MySQL