jsonb : find row by array object attribute - Mailing list pgsql-general

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