Unnesting and querying JSON data in Postgres with Examples

Here are some examples of how to unnest and query JSON data in PostgreSQL:

Unnesting simple JSON array data

Suppose we have a table mytable with a column data containing JSON data in the following format:

{
  "name": "John",
  "age": 30,
  "hobbies": ["reading", "hiking", "gaming"]
}

To unnest the hobbies array into separate rows and query the data, we can use the following SQL code:

SELECT data->>'name' AS name, data->>'age' AS age, hobby
FROM mytable, jsonb_array_elements(data->'hobbies') hobby;

This will produce the following output:

Querying JSON data with the -> operator

Suppose we have a table mytable with a column data containing JSON data in the following format:

{
  "name": "John",
  "age": 30,
  "address": {
    "street": "123 Main St",
    "city": "Anytown",
    "state": "CA",
    "zip": "12345"
  }
}

To query the street field in the address object, we can use the following SQL code:

SELECT data->'address'->>'street' AS street
FROM mytable;

This will produce the following output:

Querying JSON data with the #>> operator

Suppose we have a table mytable with a column data containing JSON data in the following format:

{
  "name": "John",
  "age": 30,
  "address": {
    "street": "123 Main St",
    "city": "Anytown",
    "state": "CA",
    "zip": "12345"
  }
}

To query the city field in the address object, we can use the following SQL code:

SELECT data#>>'{address,city}' AS city
FROM mytable;

This will produce the following output:

Querying JSON data with the jsonb_each function

Suppose we have a table mytable with a column data containing JSON data in the following format:

{
  "name": "John",
  "age": 30,
  "address": {
    "street": "123 Main St",
    "city": "Anytown",
    "state": "CA",
    "zip": "12345"
  }
}

To unnest the address object into separate rows and query the data, we can use the following SQL code:

SELECT data->>'name' AS name, data->>'age' AS age, key AS address_field, value AS address_value
FROM mytable, jsonb_each(data->'address');

This will produce the following output: