Postgresql query array of objects in JSONB field

by

There is no single operation, which can help you, but you have a few options:

1. If you have a small (and fixed) number of ids to query, you can use multiple containment operators @> combined with or; f.ex.:

where data @> '[{"id": "1884595530"}]' or data @> '[{"id": "791712670"}]'

A simple gin index can help you on your data column here.

2. If you have variable number of ids (or you have a lot of them), you can use json[b]_array_elements() to extract each element of the array, build up an id list and then query it with the any-containment operator ?|:

select *
from   jsonbtest
where  to_json(array(select jsonb_array_elements(data) ->> 'id'))::jsonb ?|
         array['1884595530', '791712670'];

Unfortunately, you cannot index an expression, which has a sub-query in it. If you want to index it, you need to create a function for it:

create function idlist_jsonb(jsonbtest)
  returns jsonb
  language sql
  strict
  immutable
as $func$
  select to_json(array(select jsonb_array_elements($1.data) ->> 'id'))::jsonb
$func$;

create index on jsonbtest using gin (idlist_jsonb(jsonbtest));

After this, you can query ids like this:

select *, jsonbtest.idlist_jsonb
from   jsonbtest
where  jsonbtest.idlist_jsonb ?| array['193623800', '895207852'];

Note: I used dot notation / computed field here, but you don’t have to.

3. But at this point, you don’t have to stick with json[b]: you have a simple text array, which is supported by PostgreSQL too.

create function idlist_array(jsonbtest)
  returns text[]
  language sql
  strict
  immutable
as $func$
  select array(select jsonb_array_elements($1.data) ->> 'id')
$func$;

create index on jsonbtest using gin (idlist_array(jsonbtest));

And query this computed field with the overlap array operator &&:

select *, jsonbtest.idlist_array
from   jsonbtest
where  jsonbtest.idlist_array && array['193623800', '895207852'];

Note: From my internal testing, this latter solution is calculated with a higher cost than the jsonb variant, but in fact it is faster than that, a little. If performance really matters to you, you should test both.

More Related Contents:

  1. How to perform update operations on columns of type JSONB in Postgres 9.4
  2. how to store PostgreSQL jsonb using SpringBoot + JPA?
  3. How to escape question mark (?) character with Spring JpaRepository
  4. PostgreSQL jsonb, `?` and JDBC
  5. Why the “Group BY” by month didn’t work? [closed]
  6. What to do with null values when modeling and normalizing?
  7. Postgres: INSERT if does not exist already
  8. postgres default timezone
  9. quick random row selection in Postgres
  10. What’s the proper index for querying structures in arrays in Postgres jsonb?
  11. How to close idle connections in PostgreSQL automatically?
  12. postgresql – replace all instances of a string within text field
  13. Is it possible to have autoincrement number without it being an ID?
  14. PostgreSQL: Modify OWNER on all tables simultaneously in PostgreSQL
  15. How do I login and authenticate to Postgresql after a fresh install?
  16. pgadmin4 : postgresql application server could not be contacted.
  17. PostgreSQL error ‘Could not connect to server: No such file or directory’
  18. Insert if not exists, else return id in postgresql
  19. function returns multiple columns as a single column instead of multiple columns
  20. Sequences not affected by transactions?
  21. Postgres: check if array field contains value?
  22. Dynamic pivot query using PostgreSQL 9.3
  23. Cursor based records in PostgreSQL
  24. Maximum characters in labels (table names, columns etc)
  25. Configuration parameter work_mem in PostgreSQL on Linux
  26. How to work with PGpoint for Geolocation using PostgreSQL?
  27. PostgreSQL INSERT ON CONFLICT UPDATE (upsert) use all excluded values
  28. input file appears to be a text format dump. Please use psql
  29. Hashing a String to a Numeric Value in PostgreSQL
  30. Spring Docker container cannot access Postgres Docker container

Leave a Comment