I have a table which stores timestamps but they're in a jsonb array column like this:

I need to the values from the start and end... and have a row per key in the json as the following:

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.

CodePudding user response：

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"]

CodePudding user response：

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