jsonb : find row by array object attribute

Поиск
Список
Период
Сортировка
От Rory Campbell-Lange
Тема jsonb : find row by array object attribute
Дата
Msg-id 20181230213112.3tjt7hejazwi7uqn@campbell-lange.net
обсуждение исходный текст
Ответы Re: jsonb : find row by array object attribute
Re: jsonb : find row by array object attribute
Список 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





В списке pgsql-general по дате отправления:

Предыдущее
От: Jeff Janes
Дата:
Сообщение: Re: getting pg_basebackup to use remote destination
Следующее
От: Tom Lane
Дата:
Сообщение: Re: jsonb : find row by array object attribute