'How create a GIN index on nested arrays in PostgeSQL

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?

postgresql


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions