From: | Rory Campbell-Lange <rory(at)campbell-lange(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | jsonb : find row by array object attribute |
Date: | 2018-12-30 21:31:12 |
Message-ID: | 20181230213112.3tjt7hejazwi7uqn@campbell-lange.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2018-12-30 21:47:10 | Re: jsonb : find row by array object attribute |
Previous Message | Jeff Janes | 2018-12-30 20:27:46 | Re: getting pg_basebackup to use remote destination |