Thread: jsonb : find row by array object attribute

With a table like this:

          Table "public.x"
     Column | Type  | Modifiers 
    --------+-------+-----------
     j      | jsonb | 

and data like this:
                            j                         
    --------------------------------------------------
     {"a": 1, "people": [{"id": 2002}, {"id": 2004}]}
     {"a": 3, "people": [{"id": 2003}, {"id": 2005}]}
    (2 rows)

I'd like to be able to find any row with a particular people id
attribute.

I can do it explitly like this:

    select * from x where j->'people'->0->'id' = '2003'::jsonb;

                            j                         
    --------------------------------------------------
     {"a": 3, "people": [{"id": 2003}, {"id": 2005}]}
    (1 row)

but that doesn't help if I need to find if any person matches the id
attribute I'm looking for.

I can get part of the way by searching like this:

    => select * from (
        select jsonb_array_elements(j #>'{people}') as jae from x
    ) y
    where jae->'id' = '2002'::jsonb;

         jae      
    --------------
     {"id": 2002}
    (1 row)

but I can't work out how to return the whole row containing a desired
people id value.

Thanks for any help
Rory

Rory Campbell-Lange <rory@campbell-lange.net> writes:
> ... I can't work out how to return the whole row containing a desired
> people id value.

Something like this, maybe?

=# select * from x where
  '2003'::jsonb in (select jsonb_array_elements(j #>'{people}')->'id');
                        j                         
--------------------------------------------------
 {"a": 3, "people": [{"id": 2003}, {"id": 2005}]}
(1 row)

It's not too efficient though :-(

            regards, tom lane

>>>>> "Rory" == Rory Campbell-Lange <rory@campbell-lange.net> writes:

 Rory> and data like this:
 Rory>                             j                         
 Rory>     --------------------------------------------------
 Rory>      {"a": 1, "people": [{"id": 2002}, {"id": 2004}]}
 Rory>      {"a": 3, "people": [{"id": 2003}, {"id": 2005}]}
 Rory>     (2 rows)

 Rory> I'd like to be able to find any row with a particular people id
 Rory> attribute.

where j @> '{"people":[{"id":2003}]}'

(meaning: j contains a key "people" whose value is an array containing
an element {"id":2003})

Since @> can use GIN indexes, you should usually try and find a search
condition using it before resorting to playing with -> or expanding out
array values.

-- 
Andrew (irc:RhodiumToad)

>>>>> "Andrew" == Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
>>>>> "Rory" == Rory Campbell-Lange <rory@campbell-lange.net> writes:

 Rory> and data like this:
 Rory> j                         
 Rory> --------------------------------------------------
 Rory> {"a": 1, "people": [{"id": 2002}, {"id": 2004}]}
 Rory> {"a": 3, "people": [{"id": 2003}, {"id": 2005}]}
 Rory> (2 rows)

 Rory> I'd like to be able to find any row with a particular people id
 Rory> attribute.

 Andrew> where j @> '{"people":[{"id":2003}]}'

 Andrew> (meaning: j contains a key "people" whose value is an array
 Andrew> containing an element {"id":2003})

or to be more precise: j is an object containing a key "people" whose
value is an array containing an element which is an object containing a
key "id" with value 2003

i.e. {"a":3, "people": [{"id":2003,"blah":123},{"id":2004}]}  would
match the condition too.

-- 
Andrew (irc:RhodiumToad)