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

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?

CodePudding user response：

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:

db<>fiddle