From: | "Enrico Thierbach" <eno(at)open-lab(dot)org> |
---|---|
To: | "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Trouble matching a nested value in JSONB entries |
Date: | 2018-06-20 19:06:30 |
Message-ID: | B4DAA03F-FF61-420B-B4BE-B9A00A1E15E7@open-lab.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi list,
I have some trouble matching a value in a JSONB object against multiple
potential matches.
Lets say, I have a table with an id, and a metadata JSONB column, which
holds data like the following
1 | {"group_id": 1}
2 | {“group_id": 1}
3 | {“group_id": 2}
4 | {“group_id": 3}
I would like to run a query which gives me the result of `SELECT id FROM
mytable WHERE metadata->>’group_id’ IN (1,2)`. Now, obviously I
could use this query, but I
would like to get away without an explicit index on
`metadata->>’group_id’`, and I was hoping to find something using
the JSONB containment operators, with support
of a gist or gin index.
The following seems to work
select * from mytable where (metadata @> '{"group_id":1}')
but only with a single value to match.
I could, of course, also “denormalize” the query a la
select * from mytable where (metadata @> '{"group_id":1}') OR
(metadata @> '{"group_id”:2}’)
but this seems to call for long execution times; also, depending on the
number of different tag names and values to match this could really
explode into quite a
large query.
Stackoverflow suggests the use of ANY
select * from mytable where (tags->'group_id' @> ANY( ARRAY
['1','3']::jsonb[] ) );
https://dba.stackexchange.com/questions/130699/postgresql-json-query-array-against-multiple-values
This seems to work - but doesn’t that require a group_id specific
index again?
Anything I overlooked?
Best,
/eno
PS: Please note that I am currently at postgres 9.5. An update, if
necessary, would be possible though.
--
me at github: https://github.com/radiospiel
me at linked.in: https://www.linkedin.com/in/radiospiel
From | Date | Subject | |
---|---|---|---|
Next Message | Hellmuth Vargas | 2018-06-20 19:21:11 | Re: Is postorder tree traversal possible with recursive CTE's? |
Previous Message | Melvin Davidson | 2018-06-20 18:44:39 | Re: Is there a way to be notified on the CREATE TABLE execution? |