Indexing object with dynamic keys within a document

SQL++
, ,
1

Have the following document as an example:

{
    "id": "05a9b954-bdee-4d7f-9715-8e9e08f8cb75",
    "type": "article",
    "translations": {
        "en": "Hello",
        "de": "Hallo"
    }
}

What would be the best way to index the values within translations dynamically? I.e. a generic index that indexes all keys within the translations object.

2

It depends on what you want query.

You want to index only keys.

CREATE INDEX ix1 ON bucket(DISTINCT OBJECT_NAMES(translations)) WHERE type = "article";

Gives document that has keys “en” or “de”

SELECT * 
FROM bucket  WHERE type = "article" AND ANY v IN  OBJECT_NAMES(translations) SATISFIES v IN ["en","de"] END;

https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/indexing-arrays.html
https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/functions.html

3

I would like to perform queries based on the value of the dynamic keys.

I.e. query all documents that have translations.en = "Hello".

How would one go about that? Much appreciated!

4

If equality predicates you can do this.

CREATE INDEX ix1 ON bucket(DISTINCT ARRAY [v.name, v.val ] FOR v IN OBJECT_PAIRS(translations) END)
                             WHERE type = "article";
SELECT * 
FROM bucket  
WHERE type = "article" 
     AND ANY v IN  OBJECT_PAIRS(translations)  SATISFIES [v.name,v.val] = ["en", "Hello"] END

For non equality predicate index either name or val then other apply post indexscan

CREATE INDEX ix2 ON bucket(DISTINCT ARRAY v.name  FOR v IN OBJECT_PAIRS(translations) END)
                             WHERE type = "article";
SELECT * 
FROM bucket  
WHERE type = "article" 
     AND ANY v IN  OBJECT_PAIRS(translations)  SATISFIES v.name  = "en" AND v.val  LIKE "Hell%" END

In this values not objects or arrays, are more distinct following might perform better because indexScan produce less items

    CREATE INDEX ix2 ON bucket(DISTINCT  OBJECT_VALUES(translations) )
                                 WHERE type = "article";
    SELECT * 
    FROM bucket  
    WHERE type = "article" 
         AND translations.en  LIKE  "Hell%"  ANY v IN  OBJECT_VALUES(translations)  SATISFIES v  LIKE "Hell%" END;

OR

  SELECT * 
    FROM bucket  
    WHERE type = "article" 
         AND translations.["en"] LIKE  "Hell%"  
        AND ANY v IN  OBJECT_VALUES(translations)  SATISFIES v  LIKE "Hell%" END;
5

The following index:

CREATE INDEX ix1 ON bucket (
    DISTINCT [v.name, v.val ]
    FOR v IN OBJECT_PAIRS(translations) END
) WHERE type = "article";

Gives the following error upon creation:

[
  {
    "code": 3000,
    "msg": "syntax error - at FOR",
    "query_from_user": "CREATE INDEX ix1 ON bucket (DISTINCT [v.name, v.val ] FOR v IN OBJECT_PAIRS(translations) END)\n                             WHERE type = \"article\";"
  }
]

What’d be the correct syntax here? Much appreciated!

6 
CREATE INDEX ix1 ON bucket (
    DISTINCT ARRAY  [v.name, v.val ]
    FOR v IN OBJECT_PAIRS(translations) END
) WHERE type = "article";
7

Many thanks. However, queries with WHERE translation.en = "Hello" do not seem to be using the index.

What could be the issue here?

8

You can’t use translation.en = “Hello”

I already gave equivalent query corresponding index. i.e.

SELECT * 
FROM bucket  
WHERE type = "article" 
     AND ANY v IN  OBJECT_PAIRS(translations)  SATISFIES [v.name,v.val] = ["en", "Hello"] END
9

I see. So there is no way to index translation.en = “Hello” directly?

10

If you want only index translation.en

CREATE INDEX ix3 ON bucket(translation.en) WHERE type = “article”