CODEWITHSUNDEEP
jsonindexingcouchbasesql++
Michał Zych
Michał Zych

Reputation: 149

How to index nested array

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);

Upvotes: 1

Views: 396

Answers (1)

vsr
vsr

Reputation: 7414

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;

Upvotes: 5

Related Questions