Wildcard indexing or Sub-document indexing

SQL++
1

Hello,

Can I create a wildcard index for the example shown below,

docId1 -> {
subDoc1: { itemId: “I1”, …},
subDoc2: { itemId: “I2”, …},
}

docId2 -> {
subDoc3: { itemId: “I3”, …},
subDoc4: { itemId: “I4”, …},
}

The index I want to create is, bucket/*/itemId

The benefits are enormous,

  1. 20MB docs that I can retrieve in one get
  2. MutateIn & LookupIn APIs give concurrent access to the document
  3. The document is internally sharded/indexed for sub doc queries

Many thanks for your reply.

With gratitude,
Neome Web

2

This how dynamic fields you index it.

CREATE INDEX ix1 ON default (DISTINCT ARRAY v.ItemId FOR v IN OBJECT_VALUES(self) END) WHERE  type = "doctype";
SELECT RAW META(d).id 
FROM default AS d
WHERE d.type = "doctype" AND ANY v IN OBJECT_VALUES(d) SATISFIES v.ItemId = "I3" END;

CREATE INDEX ix2 ON default (ALL ARRAY v.ItemId FOR v IN OBJECT_VALUES(self) END) WHERE  type = "doctype";
SELECT DISTINCT RAW META(d).id 
FROM default AS d
UNNEST OBJECT_VALUES(d) AS v
WHERE d.type = "doctype" AND v.ItemId = "I3";
1 Like
3

Interesting :slight_smile:

Thanks! Let me try…