Search Postgresql Archives

Trouble matching a nested value in JSONB entries

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux