I have a table which stores timestamps but they're in a jsonb array column like this:
| id | cycles |
|---|---|
| 5 | [{"end": "2022-10-18T18:31:34.529667Z", "start": "2022-10-05T19:01:51.400124Z"}, {"start": "2022-10-28T08:27:13.682084Z"}] |
| 6 | [{"start": "2022-10-03T16:37:38.119236Z"}] |
| 7 | [{"end": "2022-11-14T11:30:17.964960Z", "start": "2022-11-08T19:20:20.413133Z"}] |
I need to the values from the start and end... and have a row per key in the json as the following:
| id | start | end |
|---|---|---|
| 5 | 2022-10-05T19:01:51.400124Z | 2022-10-18T18:31:34.529667Z |
| 5 | 2022-10-28T08:27:13.682084Z | |
| 6 | 2022-10-03T16:37:38.119236Z | |
| 7 | 2022-11-08T19:20:20.413133Z | 2022-11-14T11:30:17.964960Z |
I've been trying with the following queries:
select
ks.id,
jsonb_path_query(KS.cycles, '$.start') AS start,
jsonb_path_query_array(KS.cycles, '$[*].start') as start
from
table ks
But when I tried to run this I'm facing the following error: ERROR: function jsonb_path_query_array(jsonb, unknown) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 84
I've been searching but haven't been able to find something related to this... and by doing the simple:
select
ks.id,
ks.cycles::jsonb ->> start
from
table ks
It doesn't work
I need help to build or change the query I'm working on.
You can use jsonb_to_recordset()
select ks.id,
r.start,
r.end
from ks
cross join jsonb_to_recordset(ks.cycles) as r("end" timestamptz, "start" timestamptz)
order by ks.id, r.start