Re: Trouble matching a nested value in JSONB entries

On Wed, Jun 20, 2018 at 10:06 PM, Enrico Thierbach <eno(at)open-lab(dot)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