Query complicated array in a JSONB in Postgres

Question

I have a JSONB field value:

{
   "status":200,
   "response":{
      "page":1,
      "limit":10,
      "total":4,
      "orders":[
         {
            "id":40201
         },
         {
            "id":40111
         }
      ]
   }
}

How do I query for the orders array object with id=40201?

Im trying to query for all rows with response->orders->[id: 40201]

The "orders array" is simply the_column -> 'response' -> 'orders' not sure what you mean with "orders array with id = ..." there are multiple IDs in the array. What exactly is the output you want? — user330315
– user330315, Commented Jul 19, 2019 at 15:40
Im trying to query for all rows with response->orders->[id: 40201] — Hackbyrd
– Hackbyrd, Commented Jul 19, 2019 at 15:42

S-Man · Accepted Answer · 2019-07-19 15:35:47Z

1

demo:db<>fiddle

If you know that this is the first object in your array (zero-based!):

SELECT
    yourjson -> 'response' -> 'orders' -> 0

If not, you have to expand your array into one row per element with jsonb_array_elements() and filter each row:

SELECT 
    elems.value
FROM 
    yourtable,
    jsonb_array_elements(yourjson -> 'response' -> 'orders') elems
WHERE
    elems ->> 'id' = '40201'

documentation

answered Jul 19, 2019 at 15:35

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

2 Comments

Hackbyrd Over a year ago

just to clarify, Im trying to query for all rows with response->orders->[id: 40201]

S-Man Over a year ago

@Hackbyrd Then you'll need the second way: dbfiddle.uk/…

user330315 · Accepted Answer · 2019-07-19 15:46:01Z

I would use an exists query for that:

select *
from the_table
where exists (select *
              from jsonb_array_elements(the_json_column -> 'response' -> 'orders') as x (o)
              where x.o ->> 'id' = 40201');

alternatively with the @> contains operator:

select *
from the_table
where exists (select *
              from jsonb_array_elements(the_json_column -> 'response' -> 'orders') as x (o)
              where x.o @> '{"id" : 40201}';

Collectives™ on Stack Overflow

Query complicated array in a JSONB in Postgres

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related