how to split a JSON column into multiple as I showed here in Postgres

Question

One of the column of my table is JSON like this:

timestamp	values
xxxx	[{"d_st":1601,"d_val":2.731},{"d_st":1602,"d_val":0.465},{"d_st":1603,"d_val":0.422},{"d_st":1604,"d_val":2.803},{"d_st":1605,"d_val":2.771},{"d_st":1606,"d_val":260.352}]

How could I split it into d_st and d_val like this:

I used this:

SELECT values ->> "d_st" AS dst, values ->> 'd_val' AS val 
FROM ss;

but can't get any value, just two empty column, why is that?

Because `->>` does 'Extracts JSON object field with the given key, as text.' and `values` is an array of objects. See examples here [JSON functions/operators](https://www.postgresql.org/docs/current/functions-json.html) — Adrian Klaver, Feb 09 '22 at 16:49
I think you need to join your outer array, have a look at the join statement in this answer: https://stackoverflow.com/a/54337777/4083475 (I don't have postgres to test and give an actually good answer atm) — edruid, Feb 09 '22 at 16:51

Anton Grig · Accepted Answer · 2022-02-09T17:31:42.963

0

for this purpose, you can use json_to_recordset or jsonb_to_recordset function depending on the data type of values column as follows:

Select ss.timestamp, x.d_st, x.d_val
From ss Cross Join Lateral json_to_recordset(ss.values) As x(d_st int, d_val numeric)

Result:

edited Feb 09 '22 at 17:31

answered Feb 09 '22 at 16:55

Anton Grig

1 Answers1