N1QL - array join

SQL++
,
1

Hi,
let’s say I have these documents:

D0

{
    "items" = ["S0", "S1"]
}

D1

{
	"items" = ["S1", "S2", "S3", "S4"]
}

D2

{
	"items" = ["S4", "S5", "S6", "S7", "S8"]
}

D3

{
	"alias" = "A1",
	"items" = ["S7", "S8", "S9", "S10"]
}

D4

{
    "alias" = "A2",
	"items" = ["S11", "S12", "S13"]
}

D5

{
	"items" = ["S10", "S20", "S30"]
}

I need to find the union of all items by alias or by one item from items array.

Example 1: find by S8

S8 → D2 union D3 union D1 (S4 is reference) union D0 (S1 is reference) union D5 (S10 is reference) → [“S4”, “S5”, “S6”, “S7”, “S8”, “S9”, “S10”, “S1”, “S2”, “S3”, “S0”, “S20”, “S30”]

Example 2: find by S5

S5 → same as S8 → [“S4”, “S5”, “S6”, “S7”, “S8”, “S9”, “S10”, “S1”, “S2”, “S3”, “S0”, “S20”, “S30”]

Example 3: find by S11

S11 → D4 → [“S11”, “S12”, “S13”]

Example 4: find by alias A1

A1-> same as S7, S8, S9 or S10 → [“S4”, “S5”, “S6”, “S7”, “S8”, “S9”, “S10”, “S1”, “S2”, “S3”, “S0”, “S20”, “S30”]

Basically any item from this example array [“S4”, “S5”, “S6”, “S7”, “S8”, “S9”, “S10”, “S1”, “S2”, “S3”, “S0”, “S20”, “S30”] should return the same result.

Is it possible to make a some kind of recursive multi-join select? Primary keys are generated as UUID + timestamp.

Thanks.

2

The expected results can be achieved with the combination of the Application and N1QL query.

SELECT ARRAY_SORT(ARRAY_DISTINCT(ARRAY_FLATTEN(ARRAY v FOR v IN ARRAY_AGG(items) WHEN (FIRST true FOR sv IN $sitems WHEN sv IN v END) END,2))) AS items FROM default;

Above Query uses named parameter $sitems.

Example 1: $sitems will be [“S8”]
Example 2 : $sitems will be [“S5”]
Example 3: $sitems will be [“S11”]
Example 4: $sitems will be out put of SELECT items FROM default WHERE alias = “A1”

Run the query if the output is NOT MISSING, NOT NULL or NOT SAME as INPUT $sitems repeat the query

The following example demonstrates in cbq shell.

INSERT INTO default VALUES ("D0", { "items" : ["S0", "S1"] }),
                    VALUES ("D1", { "items" : ["S1", "S2", "S3", "S4"] }),
                    VALUES ("D2", { "items" : ["S4", "S5", "S6", "S7", "S8"] }),
                    VALUES ("D3", { "alias" : "A1", "items" : ["S7", "S8", "S9", "S10"] }),
                    VALUES ("D4", { "alias" : "A2", "items" : ["S11", "S12", "S13"] }),
                    VALUES ("D5", { "items" : ["S10", "S20", "S30"] });

PREPARE p1 FROM SELECT ARRAY_SORT(ARRAY_DISTINCT(ARRAY_FLATTEN(ARRAY v FOR v IN ARRAY_AGG(items) WHEN (FIRST true FOR sv IN $sitems WHEN sv IN v END) END,2))) AS items FROM default;
\set -$sitems ["S8"];
EXECUTE p1;
    "results": [ { "items": [ "S10", "S4", "S5", "S6", "S7", "S8", "S9" ] } ]
\set -$sitems [ "S10", "S4", "S5", "S6", "S7", "S8", "S9" ];
EXECUTE p1;
     "results": [ { "items": [ "S1", "S10", "S2", "S20", "S3", "S30", "S4", "S5", "S6", "S7", "S8", "S9" ] } ]
\set -$sitems [ "S1", "S10", "S2", "S20", "S3", "S30", "S4", "S5", "S6", "S7", "S8", "S9" ];
EXECUTE p1;
     "results": [ { "items": [ "S0", "S1", "S10", "S2", "S20", "S3", "S30", "S4", "S5", "S6", "S7", "S8", "S9" ] } ]
\set -$sitems [ "S0", "S1", "S10", "S2", "S20", "S3", "S30", "S4", "S5", "S6", "S7", "S8", "S9" ]
EXECUTE p1;
     "results": [ { "items": [ "S0", "S1", "S10", "S2", "S20", "S3", "S30", "S4", "S5", "S6", "S7", "S8", "S9" ] } ]