Join against nested elements

SQL++
1

Hi,
I am joining two different type of documents: one is part definition with part number.
Another is category document with partnumber in second level nested element.
Common key is partnumber.
Final result is to fetch category document with partname from part definition document along with its partnumber.

Here is the query so far but it wouldnt work: Any help is appreciated very much. Thanks in advance

SELECT RAW category.*, partdef.name FROM preludesys AS category
unnest category.subcontainers sub
unnest sub.parts parts
USE KEYS ‘partNumber’
NEST preludesys as partdef
ON KEY partdef.partNumber FOR parts
WHERE meta(category).id > ‘category_’ and meta(category).id < ‘category_x’
AND substr(meta(category).id, -9, 9) != ‘AuditLog’
AND meta(partdef).id > 'partdef’ and meta(partdef).id < ‘partdef_x’
AND substr(meta(partdef).id, -9, 9) != ‘_AuditLog’
LIMIT 10

Sample documents:
partdef_00012893f5f348279ae35d27c27a57a6

{
“$$meta”: {},
“barcode”: “855506000”,
“lotRequired”: false,
“name”: “2.5MM X 70MM K WIRES”,
“partNumber”: “243506000”
}

category_0038768ec6bc4ad68ccdc81d44bd31e7

{
“name”: “ALTRX OPTIMIZED LINERS”,
“subcontainers”: [
{
“name”: “Tray 1”,
“parts”: [
{
“partNumber”: “122128050”,
“quantity”: 1
},
{
“partNumber”: “122132048”,
“quantity”: 1
},
{
“partNumber”: “243506000”,
“quantity”: 1
},
{
“partNumber”: “122136056”,
“quantity”: 1
},
{
“partNumber”: “122136058”,
“quantity”: 1
},
{
“partNumber”: “950501021”,
“quantity”: 1
}
]
}
]
}

Expected Result:
category_0038768ec6bc4ad68ccdc81d44bd31e7

{
“name”: “ALTRX OPTIMIZED LINERS”,
“subcontainers”: [
{
“name”: “Tray 1”,
“parts”: [
{
“partNumber”: “243506000”,
“name”: “2.5MM X 70MM K WIRES”,
“quantity”: 1
},
{
“partNumber”: “122128050”,
“name”: “name asdfd asdf”,
“quantity”: 1
},
{
“partNumber”: “122132048”,
“name”: “ddme asdfd asdf”,
“quantity”: 1
},
{
“partNumber”: “122136056”,
“name”: “ame asdfd asdf”,
“quantity”: 1
},
{
“partNumber”: “122136058”,
“name”: “rame asdfd asdf”,
“quantity”: 1
},
{
“partNumber”: “950501021”,
“name”: “name asd”,
“quantity”: 1
}
]
}
]
}

2 
SELECT category.*, ARRAY OBJECT_PUT(s, "parts", ARRAY OBJECT_PUT(p,"name", FIRST pd.name
                                                                              FOR pd IN partdefs
                                                                              WHEN p.partNumber = pd.partNumber
                                                                              END)
                                                   FOR p IN s.parts
                                                   END)
                      FOR s IN category.subcontainer
                      END AS subcontainers
FROM preludesys AS category
LET partdefs = (SELECT partdef.partNumber, partdef.name
                FROM preludesys AS partdef USE KEYS ARRAY_FLATTEN(category.subcontainer[*].parts[*].partNumber,1)
                WHERE META(partdef).id > "partdef_" AND meta(partdef).id < "partdef_x"
                      AND SUBSTR(META(partdef).id, -9, 9) != "AuditLog")
WHERE META(category).id > "category_" AND meta(category).id < "category_x"
      AND SUBSTR(META(category).id, -9, 9) != "AuditLog"
LIMIT 10;
4

In your query, USE KEYS doesn’t return values because partNumber is not document key, pls see my question’s sample documents - the document key starts with “partdef_”
Also I am doubtful about the ARRAY_FLATTEN’s output’s matching with category.
I am puzzled why ANSI JOIN doesn’t work for me:

SELECT *
FROM bucketname c
JOIN bucketname p
ON p.partNumber in c.subcontainers[0].parts.partNumber
WHERE meta©.id like ‘category%’ and meta©.id not like ‘%_AuditLog’
and meta§.id like ‘part%’ and meta§.id not like ‘%_AuditLog’

I know the ON condition may be incorrect syntax, but I get error in all other ways too.

5

If you don’t have relation through document key, Use ANSI Join.
Check Example 13 https://blog.couchbase.com/ansi-join-support-n1ql/
To get desired output you may have to use GROUP BY.

CREATE IDEX ix1 ON preludesys(partNumber) WHERE META().id > "partdef_" AND meta().id < "partdef_x";
SELECT RAW OBJECT_PUT(d.category, "subcontainer", scs)
FROM (
      SELECT category, OBJECT_PUT(sc, "parts", parts) AS sc
      FROM preludesys AS category
      UNNEST category.subcontainer AS sc
      UNNEST sc.parts AS cp
      JOIN preludesys AS partdef ON cp.partNumber = partdef.partNumber
      WHERE META(category).id > "category_" AND meta(category).id < "category_x"
            AND SUBSTR(META(category).id, -9, 9) != "AuditLog"
            AND META(partdef).id > "partdef_" AND meta(partdef).id < "partdef_x"
            AND SUBSTR(META(partdef).id, -9, 9) != "AuditLog"
      GROUP BY category, sc
      LETTING parts = ARRAY_AGG(OBJECT_PUT(cp, "name", partdef.name))) AS d
GROUP BY d.category
LETTING scs = ARRAY_AGG(d.sc);
6

Getting same error as I received for my query:
“syntax error - at cp”

Please note that sc.parts is an array. I think that’s the issue.

7

ANSI JOIN requires CB 5.50 or higher.

CB 5.50+

CREATE IDEX ix1 ON preludesys(partNumber) WHERE META().id > "partdef_" AND meta().id < "partdef_x";
SELECT RAW OBJECT_PUT(d.category, "subcontainer", scs)
FROM (
      SELECT category, OBJECT_PUT(sc, "parts", parts) AS sc
      FROM preludesys AS category
      UNNEST category.subcontainer AS sc
      UNNEST sc.parts AS cp
      JOIN preludesys AS partdef ON cp.partNumber = partdef.partNumber
      WHERE META(category).id > "category_" AND meta(category).id < "category_x"
            AND SUBSTR(META(category).id, -9, 9) != "AuditLog"
            AND META(partdef).id > "partdef_" AND meta(partdef).id < "partdef_x"
            AND SUBSTR(META(partdef).id, -9, 9) != "AuditLog"
      GROUP BY category, sc
      LETTING parts = ARRAY_AGG(OBJECT_PUT(cp, "name", partdef.name))) AS d
GROUP BY d.category
LETTING scs = ARRAY_AGG(d.sc);

PRE CB 5.50 No relation through document key and partdefs are limited (Cartesian join).

SELECT category.*, ARRAY OBJECT_PUT(s, "parts", ARRAY OBJECT_PUT(p,"name", FIRST pd.name
                                                                              FOR pd IN partdefs
                                                                              WHEN p.partNumber = pd.partNumber
                                                                              END)
                                                   FOR p IN s.parts
                                                   END)
                      FOR s IN category.subcontainer
                      END AS subcontainers
FROM preludesys AS category
LET partdefs = (SELECT partdef.partNumber, partdef.name
                FROM preludesys AS partdef 
                WHERE META(partdef).id > "partdef_" AND meta(partdef).id < "partdef_x"
                      AND SUBSTR(META(partdef).id, -9, 9) != "AuditLog")
WHERE META(category).id > "category_" AND meta(category).id < "category_x"
      AND SUBSTR(META(category).id, -9, 9) != "AuditLog"
LIMIT 10;
8

Thank you. I have 5.1 community edition.