Question 90259 in PostgreSQL Question posted in
The official documentation can be found here.

Transform data from a jsonb column into a table in postgresql

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.

Tags:

2

Answers


  1. 0

    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"]
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search