Array indexing question

SQL++
1

Hi, I need some help with creating indexes.
I have a document that looks like below. In the tagMap field I have already created indexes on the individual arrays, the indexes work well but what I need is, instead of creating a separate index every time I want a new tagMap I would like just one index on tagMap. I can also use the tags field which has the tagMap info as an array.

{
 "id": "id_c7811bcc1f22",
  "appsUserIds": [
    "id_c7811bcc1f22"
  ],
  "tagMap": {
    "hotelRoomPreferences": [
      {
        "key": "hotelRoomPreferences",
        "value": "Extra Pillows Requested",
        "lastChanged": 1474965818356,
        "default": false
      }
    ],
    "bedConfiguration": [
      {
        "key": "bedConfiguration",
        "value": "Twin",
        "lastChanged": 1474970212777,
        "default": false
      }
    ]
  },
  "externalIds": {},
  "version": 1474970207545524200,
  "tags": [
    {
      "key": "hotelRoomPreferences",
      "value": "Extra Pillows Requested",
      "lastChanged": 1474965818356,
      "default": false
    },
    {
      "key": "bedConfiguration",
      "value": "Twin",
      "lastChanged": 1474970212777,
      "default": false
    }
  ],
  "dataSync": {}
}

These are the individual indexes that I have created and they work well.

CREATE INDEX `tagMapBedConfig` ON write ((DISTINCT (ARRAY (x.`value`) FOR x IN tagMap.bedConfiguration END))) USING GSI;
CREATE INDEX `tagMapHotelRoomPref` ON write ((DISTINCT (ARRAY (x.`value`) FOR x IN tagMap.hotelRoomPreferences END))) USING GSI;

The call at the moment looks like this:

select * from write where any x in tagMap.hotelRoomPreferences satisfies x.`value` = 'Extra Pillows Requested' END ;

What I am looking for is something like this.

CREATE INDEX `tagMap` ON write ((DISTINCT (ARRAY (x.`value`) FOR x IN tagMap.x END)))
2

Try WITHIN.

CREATE INDEX idx_tagMap ON write (DISTINCT ARRAY x.`value` FOR x WITHIN tagMap END);

EXPLAIN SELECT *
FROM write
WHERE ANY x WITHIN tagMap SATISFIES x.`value` = 'Extra Pillows Requested' END;
3

Thanks geraldss, That works great.

1 Like