CODEWITHSUNDEEP
postgresql
SoWhat
SoWhat

Reputation: 5622

Extract jsonb fields as rows

I have a table users name: varchar(20) data:jsonb

Records look something like this

adam, {"car": "chevvy", "fruit": "apple"}
john, {"car": "toyota", "fruit": "orange"}

I want to extract all the fields like this

name.    |.type      |.  value
adam.      car         chevrolet
adam.      fruit       apple
john.      car       toyota
john.      car       orange

Upvotes: 0

Views: 43

Answers (1)

Ronaldo Ferreira de Lima
Ronaldo Ferreira de Lima

Reputation: 404

For your example you can do:

SELECT name, d.key AS type, d.value
  FROM users u,
       JSONB_EACH_TEXT(u.data) AS d
;

output:

 name | type  | value  
------+-------+--------
 adam | car   | chevvy
 adam | fruit | apple
 john | car   | toyota
 john | fruit | orange
(4 rows)

There are good explanations here PostgreSQL - jsonb_each

Upvotes: 1

Related Questions