I have a table with column "data" of jsonb type. The structure looks like this (synthetic simplified example):
[
{
"object": "car",
"fields": [
{ "id": "price", "value": "200000" },
{ "id": "weight", "value": "600" },
{ "id": "color", "value": "green" }
]
},
{
"object": "wheel1",
"fields": [
{ "id": "diameter", "value": "16" },
{ "id": "height", "value": "20" }
]
},
{
"object": "wheel2",
"fields": [
{ "id": "diameter", "value": "17" },
{ "id": "height", "value": "30" }
]
}
]
The goal is to find rows which has objects with some field properties like so:
SELECT * FROM cars
WHERE data @> '[{"fields": [{"id":"diameter", "value":"16"}, {"id":"height", "value":"20"}]}]'
AND data @> '[{"fields": [{"id":"price", "value":"200000"}, {"id":"weight", "value":"600"}]}]';
EXPLAIN of course says that Seq Scan is involved. I know how to create GIN index on objects in array:
CREATE INDEX cars_data_idx ON cars USING gin ((data->'some_array') jsonb_path_ops);
However I'm unable to create a proper index on "fields" arrays, which are in objects inside main array. Is it generally possible? What is the correct syntax for that?