Finding the matched index "key" on a complex N1QL query

SQL++
, ,
1

I have a fairly complex index, based on flattened arrays which sit inside a map.
My data looks similar to this:

doc example 1:
{
“type”: “brand”,
“brand_id”: “1”,
“brand_name”: “Pharmacuticals inc.”,
“branches”: [“phM”, “sph”, “ph11”],
“branch_products”: {
“phM”: [“shampoo”, “soap”],
“sph”: [“soap”],
“ph11”: [“toothpaste”]
}
}

doc example 2:
{
“type”: “brand”,
“brand_id”: “2”,
“brand_name”: “World’s best supermarket”,
“branches”: [“sup12”, “superduper”],
“branch_products”: {
“sup12”: [“peanuts”, “soap”],
“superduper”: [“shampoo”]
}
}

It can be assumed that:
Each brand_id is unique
Each key in “branch_products” map (also each element in “branches” array) is unique both in the scope of the document and outside of it (2 brands cannot have any of the same branches).

While this may not be the best way to sort my data, it cannot be changed right now.

I need to search which branches contain a certain product. Sometimes I will want more information, such as brand_id/brand_name, but I often just want the branch.
For example, when searching for “soap”, I want to be able to get: [“phM”, “sph”, “sup12”].

I created an index that flattens “branch_products” into a string of " product:branch".
This is the definition:
CREATE INDEX my_index ON my_bucket
((DISTINCT (ARRAY_FLATTEN (ARRAY
(ARRAY ((product) || “:” || (pair.name)) FOR product IN (pair.val) END)
FOR pair IN OBJECT_PAIRS(branch_products) END, 1)))) WHERE (type = “branch”)

(I have also considered using the array format ([product, branch]), but I’m stuck on the same issue either way)

My question is:
When querying by product, how can I get “branch” without opening the document? Is it possible to just receive the “key” that the index matched on?

I am using Enterprise Edition 6.0.2

Thank you,
Zimri

2

Hi @ZimriCB ,

CREATE INDEX my_index ON my_bucket ( ALL ARRAY_FLATTEN(ARRAY (ARRAY CONCAT(v1, ":", p) FOR v1 IN v END) FOR p:v IN branch_products END,1), brand_id, brand_name)
WHERE type = "brand";

SELECT RAW SPLIT(u,":")[1]
FROM my_bucket AS b
UNNEST ARRAY_FLATTEN(ARRAY (ARRAY CONCAT(v1, ":", p) FOR v1 IN v END) FOR p:v IN b.branch_products END,1) AS u
WHERE b.type = "brand" AND u LIKE "soap:%";

SELECT SPLIT(u,":")[1] AS product, b.brand_id, b.brand_name, META(b).id
FROM my_bucket AS b
UNNEST ARRAY_FLATTEN(ARRAY (ARRAY CONCAT(v1, ":", p) FOR v1 IN v END) FOR p:v IN b.branch_products END,1) AS u
WHERE b.type = "brand" AND u LIKE "soap:sup12";

See if the query is covered.

3

Thank you for your speedy reply!