Index on dynamic key/value pairs in a metadata object

SQL++
,
1

What would be the appropriate way to index on dynamic key/value pairs in an object? I need to be able to query for a specific field within the document based on a user-inputted key/value pairing that is located within an object in the document. An example of the object would be:

{
  "metaData": {
    "id": "123",
    "source": "survey"
  },
  "surveyTags": ["fun", "long", "boring"],
  "accountId": "0011b71f-2b68-414d-b99b-f40188a1561f"
}

The kicker is that the contents of the metaData object can be distinct to each document itself (dynamic). So you could have different queries for any given document. Two examples queries that should work for the document above would look like:

SELECT accountId
FROM bucket
USE INDEX(metadata_idx)
WHERE metaData.id = "123"

OR

SELECT accountId
FROM bucket
USE INDEX(metadata_idx)
WHERE metaData.source = "survey"

I would think to use adaptive indexing (https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/adaptive-indexing.html) and index on the metaData object itself so something like:

CREATE INDEX `metadata_idx`
ON `bucket`(DISTINCT PAIRS(metaData), accountId)
WHERE metaData IS VALUED
AND accountId IS VALUED

Any suggestions/improvements on the above would be appreciated!

2

I am not sure if above thing will work. If it is PAIRS(self) it will index whole document and will work. If you are indexing field PARIS(metaData), Index will only chosen when query predicate on metaData not sub fields.

If your conditions are equality on metaData you can use the following approach.

CREATE INDEX `metadata_idx` ON `default`(DISTINCT ARRAY [v.name,v.val] FOR v IN OBJECT_PAIRS(metaData) END , accountId);

EXPLAIN SELECT accountId 
FROM default USE INDEX(metadata_idx) 
WHERE ANY v IN OBJECT_PAIRS(metaData) SATISFIES [v.name, v.val] = ["source", "survey"] END;