CODEWITHSUNDEEP
sqljsonpostgresql
HackToHell
HackToHell

Reputation: 2393

checking json array with postgres

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 ?

Upvotes: 0

Views: 187

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51529

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

Upvotes: 1

Related Questions