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 по дате отправления: