JSON split in Postgres

Question

How we can split this in a single query, Any ideas or suggestions ?

{"_xxx8430yyy59_xx":{"label":"Campaign Codes"},"_zzz984ggg4110_zzz":{"label":"NL Discount Codes"},"_ttt9843hhh160_ttt":{"label":"OOP Discount Codes"},"_ddd984393lll3_ddd":{"label":"Influencer Codes (Offer)"} }

I have tried to do this in multiple statements , but was not successful.

WITH CTE AS(select *,  unnest(string_to_array(value, ',')) AS parts from config_data)
SELECT parts from CTE

Expected output as

ID                      label
_xxx8430yyy59_xx         Campaign Codes
_zzz984ggg4110_zzz        NL Discount Codes
_ttt9843hhh160_ttt        OOP Discount Codes
_ddd984393lll3_ddd        Influencer Codes (Offer)

Bergi · Accepted Answer · 2022-08-12 12:46:36Z

2

You don't string-split anything, you use the JSON processing functions that are built into postgres:

SELECT key as "ID", obj.value->>'label' AS label
FROM config_data,
jsonb_each(config_data.value::jsonb) AS obj(key, value)

You should also change the data type of the value column in your table to be jsonb/json, if you haven't already.

answered Aug 12, 2022 at 12:46

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

JSON split in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related