Query json data stored in text datatype in postgresql-CodePudding

I have two Tables tableA and table B in tableA json struct is stored in a column in the form

{"action":"UPDATED","atime":1672914675,"category":"homes","ctime":1672915282,"uid":0,"hashed":1}

and an json array stored in tableB in a text column in the form

[{"description":"rooaa","uid":0,"user_name":opq}, {"description":"dassaon","uid":1,"user_name":oqqq}]

i want do join operations with the fields uid in tableA and its respective username in the tableB ,can someone tell me how i can do it.i just want to join the username from tableB for the specific value of uid in table A.

CodePudding user response：

First flatten the json fields of tableA and tableB in CTEs t1 and t2 - and then do a routine join. Please note that this table design is horribly inefficient and hard to work with.

with t1 as 
(
 select jsonfield::json ->> 'uid' as uid, jsonfield::json as json_a from tablea
), 
t2 as 
(
 select json_b, json_b ->> 'uid' as uid, json_b ->> 'user_name' as user_name 
 from (select json_array_elements(jsonarr::json) json_b from tableb) as t
)
select uid, user_name, json_a, json_b 
from t1 
join t2 using (uid);

DB-Fiddle demo

CodePudding user response：

try something like this :

SELECT *
  FROM tableA AS a
 INNER JOIN 
     ( tableB AS b
       CROSS JOIN LATERAL jsonb_path_query(b.text_column :: jsonb, '$[*]') AS c(json_value)
     ) AS d
    ON a.text_column :: jsonb->'uid' = d.json_value->'uid'