jsonb : find row by array object attribute

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

Responses

Browse pgsql-general by date

  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