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)