Extract jsonb fields as rows-CodePudding

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

CodePudding user response：

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