用于嵌套数组查询的Couchbase索引

{
  "id": "user::123",
  "name": "John Doe",
  "orders": [
    {
      "orderId": "order::1",
      "items": [
        {
          "itemId": "item::1",
          "quantity": 2
        },
        {
          "itemId": "item::2",
          "quantity": 1
        }
      ]
    },
    {
      "orderId": "order::2",
      "items": [
        {
          "itemId": "item::3",
          "quantity": 3
        }
      ]
    }
  ]
}

CREATE INDEX idx_orders_items ON `bucket_name`(DISTINCT ARRAY i.itemId FOR i IN orders.items END) WHERE type = 'user';

SELECT u.id, u.name, o.orderId, i.itemId, i.quantity
FROM `bucket_name` u
JOIN orders o ON KEYS ARRAY [u.id || '::' || o.orderId] FOR o IN u.orders END
JOIN orders.items i ON KEYS ARRAY [o.orderId || '::' || i.itemId] FOR i IN o.items END
WHERE u.type = 'user' AND i.itemId = 'item::1';

CREATE FULLTEXT INDEX idx_fts_orders_items ON `bucket_name`(orders.items.itemId) WHERE type = 'user';

SELECT * FROM `bucket_name` WHERE orders.items.itemId MATCH 'item::1' AND type = 'user';