How create a GIN index on nested arrays in PostgeSQL

Viewed 87

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?

0 Answers
Related