Join tables with values from JSON-CodePudding

Im having problems querying data from json fields.

I have some json format columns saved as text in my postgres database (version 10)

Sometimes i need to be able to join two tables on json values.

I have no idea how i can do this..

Here is an simple example.

In my select i want to output the fruit and the color.

I have the color_cd number inside a json in the fruits table and i can find the color inside another json in the code_table.

My wished output should be like this

Fruit_ID   Name     Color
1000       Pear     Green
1001       Banana   Yellow

Fiddle link --> https://dbfiddle.uk/?rdbms=postgres_13&fiddle=3f989db0524e288183619bab63fc9add

CodePudding user response：

Your column records on table code_table has problem in json data and i fixed problem and change to below format:

{"color_cd":{"30":{"code":"30","color":"yellow"},"55":{"code":"55","color":"green"},"60":{"code":"60","color":"red"}}}

You can see query structure and result in dbfiddle


select
 f.id,
 f.name,
 j_cd.value ->> 'color' as color
from 
  code_table ct 
  cross join jsonb_each(records::jsonb -> 'color_cd') j_cd
  inner join fruits f on f.type_cd :: jsonb ->> 'color_cd' = j_cd.key::text
where 
  ct.name = 'color_cd'