Reputation: 519
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
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
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