CODEWITHSUNDEEP
jsonpostgresqlparsingpyspark
Shan
Shan

Reputation: 53

Json - Flatten Key and Values in pyspark

In a table having JSON column value as: {"XXX": ["123","456"],"YYY": ["246","135"]}

{"XXX": ["123","456"],"YYY": ["246","135"], "ZZZ":["333","444"]}

Need to flatten it as

Key Value
XXX 123
XXX 456
YYY 246
YYY 135
ZZZ 333
ZZZ 444

Suggestion please..

select key , value , from table, json_each(xref_json::json)

Got this result as, need to achieve the above result

Key Value
XXX ["123","456"]
YYY ["246","135"]

Upvotes: 0

Views: 167

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246163

You use lateral joins for that:

SELECT x.key, y.elem
FROM mytable
   CROSS JOIN LATERAL jsonb_each(mytable.xref_json::jsonb) AS x(key,value)
   CROSS JOIN LATERAL jsonb_array_elements(x.value) AS y(elem)
WHERE x.key IN ('XXX', 'YYY');

Upvotes: 1

Related Questions