Transform data from a jsonb column into a table in postgresql

Question

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.

What version of Postgres?

Adrian Klaver
– Adrian Klaver

2023-01-06 01:40:03 +00:00
Commented Jan 6, 2023 at 1:40 — Adrian Klaver
– Adrian Klaver, Commented Jan 6, 2023 at 1:40

user330315 · Accepted Answer · 2023-01-06 07:56:58Z

2

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

answered Jan 6, 2023 at 7:56

user330315

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

Comments

Adrian Klaver · Accepted Answer · 2023-01-06 01:38:21Z

Works for me with changing jsonb_path_query_array to jsonb_path_query for the end values:

create table ks (id integer, cycles jsonb);

insert into ks values (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"}]');

select  
    ks.id,  
    jsonb_path_query(KS.cycles, '$.start') AS start,    
    jsonb_path_query(KS.cycles, '$.end') as end  
from    
    ks  ;
 id |             start             |              end              
----+-------------------------------+-------------------------------
  5 | "2022-10-05T19:01:51.400124Z" | "2022-10-18T18:31:34.529667Z"
  5 | "2022-10-28T08:27:13.682084Z" | NULL
  6 | "2022-10-03T16:37:38.119236Z" | NULL
  7 | "2022-11-08T19:20:20.413133Z" | "2022-11-14T11:30:17.964960Z"

Also your original query works:

select  
    ks.id,  
    jsonb_path_query(KS.cycles, '$.start') AS start,    
    jsonb_path_query_array(KS.cycles, '$[*].start') as end  
from    
    ks  ;
 id |             start             |                              end                               
----+-------------------------------+----------------------------------------------------------------
  5 | "2022-10-05T19:01:51.400124Z" | ["2022-10-05T19:01:51.400124Z", "2022-10-28T08:27:13.682084Z"]
  5 | "2022-10-28T08:27:13.682084Z" | ["2022-10-05T19:01:51.400124Z", "2022-10-28T08:27:13.682084Z"]
  6 | "2022-10-03T16:37:38.119236Z" | ["2022-10-03T16:37:38.119236Z"]
  7 | "2022-11-08T19:20:20.413133Z" | ["2022-11-08T19:20:20.413133Z"]

Collectives™ on Stack Overflow

Transform data from a jsonb column into a table in postgresql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related