CODEWITHSUNDEEP
postgresql
morfair
morfair

Reputation: 519

How to use IN for search in JSON-b with index?

I have table with JSON-b field like this:

id | data
----------
1  | '{"points": [{"id": 10, "address": "Test 1"}, {"id": 20, "address": "Test 2"}, {"id": 30, "address": "Test 3"}]}'
2  | '{"points": [{"id": 40, "address": "Test 444"}, {"id": 20, "address": "Test 222"}, {"id": 50, "address": "Test 555"}]}'

The JSON-b field "data" contains "points" array.

How to get all "points" whose point id is contained in an array [40, 20]? Like classic IN:

... IN (40,20)

Query must use GIN index!!! Array IDs will be sub-query.

Upvotes: 0

Views: 280

Answers (2)

jjanes
jjanes

Reputation: 44423

You could almost do it with a functional index using a jsonb_path_query_array to extract the data. But as far as I can tell, not quite.

create index on t using gin (jsonb_path_query_array(x,'$.points[*].id'));

And then query with:

select * from t where jsonb_path_query_array(x,'$.points[*].id') ?| '{20,40}';

The problem is that ?| only works with text elements, while in your data the values of 'id' are integers, not text. I thought jsonpath would provide a way to convert them to text, but if it does, I cannot find it.

So instead I think you will have to define your own function which accepts jsonb, and returns int[] or text[] (or jsonb which is an array of text conversions). Then you can build an index on the results of this function. Don't forget to declare it immutable.

Upvotes: 1

user330315
user330315

Reputation:

You will need to unnest the array (essentially normalizing your data model "on-the-fly") then you can use a subquery to check the value:

select t.*
from the_table t
where exists (select *
              from jsonb_array_elements(t.data -> 'points') as x(element)
              where (x.element ->> 'id')::int in (select id 
                                                  from other_table))

Upvotes: 0

Related Questions