How to Index this query with dynamic field names

SQL++
1

Hello, I have the following data structure, and I need to be able to query the custom_fields property of the document. Here is an example of the document:

{
    "custom_fields": {
        "e7f27b73-204a-4a9d-bfb2-480546b2f193": [
            "Peach",
            "Apple"
        ]
    }
}

The query itself has to find documents that match both the field name “e7f27b73-204a-4a9d-bfb2-480546b2f193” and contain a specific value “Peach”.

The query below works if there is a PRIMARY index:

SELECT * FROM `higherrealm` WHERE
ANY f in object_pairs(`custom_fields`) SATISFIES f.name == 'e7f27b73-204a-4a9d-bfb2-480546b2f193' AND 
ANY i IN f.val SATISFIES i == "Peach" END END

However, I need an appropriate index for these queries. The closest I’ve been able to come is an array index on the object pairs ‘name’ property, but this doesn’t fully work for the above query.

What makes this difficult is that the field name is dynamic for each field in custom_fields.

This index will index the field name but not the values:

CREATE INDEX `idx_custom_fields_name`
ON `bucket` (DISTINCT ARRAY f.name FOR f IN OBJECT_PAIRS(`custom_fields`) END)

But I need to be able to query against name and the array of values, using an index.

Any help is much appreciated. Thanks!

2

If you are looking ONLY equality, you can use the following approach.

CREATE INDEX ix1 ON `higherrealm` (DISTINCT ARRAY (DISTINCT ARRAY [f.name, fv] FOR fv IN f.val END) FOR f IN OBJECT_PAIRS(`custom_fields`) END);

SELECT h.*
FROM `higherrealm` AS h
WHERE ANY f IN OBJECT_PAIRS(h.`custom_fields`)
      SATISFIES (ANY fv IN f.val
                 SATISFIES [f.name, fv] == ["e7f27b73-204a-4a9d-bfb2-480546b2f193","Peach"]
                 END )
      END;
1 Like
3

That is extremely helpful, thanks!