Обсуждение: Trouble matching a nested value in JSONB entries
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
On Wed, Jun 20, 2018 at 10:06 PM, Enrico Thierbach <eno@open-lab.org> wrote: > 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. Upgrade, please ! I have only master 11beta2 right now: select * from qq where js @> '{"group_id":1}'; id | js ----+----------------- 1 | {"group_id": 1} 2 | {"group_id": 1} (2 rows) > > -- > me at github: https://github.com/radiospiel > me at linked.in: https://www.linkedin.com/in/radiospiel -- Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
Oleg,
1 | {"group_id": 1}
2 | {“group_id": 1}
3 | {“group_id": 2}
4 | {“group_id": 3}PS: Please note that I am currently at postgres 9.5. An update, if
necessary, would be possible though.Upgrade, please !
I have only master 11beta2 right now:
select * from qq where js @> '{"group_id":1}';
id | js
----+-----------------
1 | {"group_id": 1}
2 | {"group_id": 1}
(2 rows)
thanks for your answer. Your code does work fine on 9.5 already; what I would like to achieve is to get records where the group_id entry is 1 or 2, and a fear that
select * from mytable where (metadata @> '{"group_id":1}') OR (metadata @> '{"group_id”:2}’)
- while doable - would not make the best use of existing indices.
Any more ideas?
Best,
/eno