How to index nested array

Question

How to index (N1QL query in Couchbase) above document to speed up searching by SerialNumber field in nested array (doc => groups => items => item.SerialNumber)?

Sample:

{
  "Id": "0012ed6e-41af-4e45-b53f-bac3b2eb0b82",
  "Machine": "Machine2",
  "Groups": [        
    {
      "Id": "0fed9b14-fa38-e511-893a-001125665867",
      "Name": "Name",
      "Items": [
        {
          "Id": "64e69b14-fa38-e511-893a-001125665867",
          "SerialNumber": "1504H365",
          "Position": 73
        },
        {
          "Id": "7be69b14-fa38-e511-893a-001125665867",
          "SerialNumber": "1504H364",
          "Position": 72
        }
      ]
    },
    {
      "Id": "0fed9b14-fa38-e511-893a-001125665867",
      "Name": "Name",
      "Items": [
        {
          "Id": "64e69b14-fa38-e511-893a-001125665867",
          "SerialNumber": "1504H365",
          "Position": 73
        },
        {
          "Id": "7be69b14-fa38-e511-893a-001125665867",
          "SerialNumber": "1504H364",
          "Position": 72
        }
      ]
    }
  ]
}

my query:

CREATE INDEX idx_serial ON `aplikomp-bucket` 
(ALL ARRAY(ALL ARRAY i.SerialNumber FOR i IN g.Items END ) FOR g In Groups END);

vsr · Accepted Answer · 2018-01-11 14:55:45Z

5

CREATE INDEX idx_serial ON `aplikomp-bucket` (DISTINCT ARRAY(DISTINCT ARRAY i.SerialNumber FOR i IN g.Items END ) FOR g In Groups END);

SELECT META().id FROM `aplikomp-bucket` AS a
WHERE ANY g IN a.Groups SATISFIES (ANY i IN g.Items SATISFIES i.SerialNumber > 123 END) END;

answered Jan 11, 2018 at 14:55

vsr

7,4441 gold badge13 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Michał Zych Over a year ago

Thx. Index seems to work, but e.g. SELECT i.SerialNumber doesn't

vsr Over a year ago

If u need to project serialNumber then try this CREATE INDEX idx_serial ON aplikomp-bucket (ALL ARRAY(DISTINCT ALL i.SerialNumber FOR i IN g.Items END ) FOR g In Groups END); SELECT i.SerialNumber FROM aplikomp-bucket AS a UNNEST a.Groups AS g UNNEST g.Items AS i WHERE i.SerialNumber > 123;

Collectives™ on Stack Overflow

How to index nested array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related