CODEWITHSUNDEEP
sqlpostgresql
Cristian Riaño
Cristian Riaño

Reputation: 21

Flatten jsonb as columns

I have a table in PostgreSQL 10.6 that looks more or less like this:

Name             │  Description        │ Data
─────────────────┼─────────────────────┼──────────────────────
Jhon             │ A guy               │ {"age": 23, "food": "Burger"}
Carl             │ Another guy         │ {"age": 35, "food": "Pizza"}
Josh             │ Again               │ {"age": 27, "food": "Burrito", "id": 3}

I would like to have a query that includes the information in the jsonb flattened.
Where every key is a new column with it's value. \

Usually all the JSONs have the same keys but could be that they have on extra key in the future, in which case showing it as NULL or not showing it at all is fine.

So the result should be something like

Name   │  Description │ Age │ Food  │ Id
───────┼──────────────┼─────┼─────-─┼──────
Jhon   │ A guy        │ 23  │Burger │ NULL
Carl   │ Another guy  │ 35  │Pizza  │ NULL
Josh   │ Again        │ 27  │Burrito│ 3

Upvotes: 1

Views: 69

Answers (2)

Cristian Riaño
Cristian Riaño

Reputation: 21

It can be done creating a type and with jsonb_populate_record

CREATE TYPE user_data AS (age int, food text, id int);

SELECT users.name, user.description, flatten.* 
FROM users, jsonb_populate_record(null::user_data, data) as flatten

Upvotes: 1

user330315
user330315

Reputation:

Use the ->> operator:

select name, 
       description,
       (data ->> 'age')::int as age,
       data ->> 'food' as food,
       data ->> 'id' as id
from the_table

Upvotes: 2

Related Questions