checking json array with postgres

Question

I have an JSONB array in postgres in the follow format.

{
   "id" : 22323,
   "details" : [
                        {
                            "status" : "stage1",
                             "timestamp" : "2017-09-89"
                         },
                        {
                            "status" : "stage2",
                             "timestamp" : "2017-09-89"
                         }
                        ]
    }

I need to get the timestamp at stage2, how do I select the particular status's timestamp in postgresql ?

if index of "details" always same as "status": "stageVALUE, you can just use ->element, but if not - you need to iterate over elements of array with json_array_elements — Vao Tsun
– Vao Tsun, Commented May 19, 2017 at 12:28
I tried select (json_column ->> 'details')::json ->> status from table and it didn't work. I got empty results. — HackToHell
– HackToHell, Commented May 19, 2017 at 12:30

Vao Tsun · Accepted Answer · 2017-05-19 12:33:13Z

1

if index of "details" always same as "status": "stageVALUE, you can just use ->element, but if not - you need to iterate over elements of array with json_array_elements, like here:

t=# with b as (with v as (select '{
   "id" : 22323,
   "details" : [
                        {
                            "status" : "stage1",
                             "timestamp" : "2017-09-89"
                         },
                        {
                            "status" : "stage2",
                             "timestamp" : "2017-09-89"
                         }
                        ]
    }'::json j)
select json_array_elements(j->'details') j from v)
select j->>'timestamp' from b where j->>'status' = 'stage2'
;
  ?column?
------------
 2017-09-89
(1 row)

and if stage2 is always second array element, the cheaper:

t=# with v as (select '{
   "id" : 22323,
   "details" : [
                        {
                            "status" : "stage1",
                             "timestamp" : "2017-09-89"
                         },
                        {
                            "status" : "stage2",
                             "timestamp" : "2017-09-89"
                         }
                        ]
    }'::json j)
select j->'details'->1->>'timestamp' from v;
  ?column?
------------
 2017-09-89
(1 row)

I use index 1 cos arrays are indexed from 0

answered May 19, 2017 at 12:33

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

4 Comments

HackToHell Over a year ago

Ah nice but the position in the JSON can unfortunately change, there fore, I tried out option 1. I made the query as with x as (select jsonb_array_elements(tracking_info -> 'details') j from tracking_data) select x ->> 'status' from x;I get ERROR: operator does not exist: record ->> unknown

HackToHell Over a year ago

Which is pretty weird and I cannot convert record to json either

Vao Tsun Over a year ago

oh that is because you showed different code :) please create another question with your plpgslq code

HackToHell Over a year ago

Nevermind, found out the mistake, thanks :) It should be using j

Collectives™ on Stack Overflow

checking json array with postgres

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related