How to join with array items?

Hello,

I’m trying to use join to include product documents into an aggregate document

Given the following documents, is there a way to use join to include product documents into Document 1?

Document 1 (aggregate.1)

{
    "id": "an id",
    "items": [
        {
            "product_id": "3"
        },
        {
            "product_id": "4"
        }
    ]
}

Product Document (product.3)

{
    "key1": "value1"
}

Product Document (product.4)

{
    "key1": "value1"
}

Desired output

{
    "id": "an id",
    "items": [
        {
            "product_id": "3"
        },
        {
            "product_id": "4"
        }
    ],
    "proudcts": [
        {
            "key1": "value1"
        },
        {
            "key1": "value1"
        }
    ]

    
}

Hi @moon0326,
this is what NEST does. Try:

SELECT ag.*, products 
FROM  aggregates   ag 
NEST products  
ON KEYS  ARRAY x.product_id FOR x IN ag.items END;

Find more in documentation at:
http://developer.couchbase.com/documentation/server/4.5/n1ql/n1ql-language-reference/from.html

And, following blog:

-Prasad

1 Like

Thank you! I was not aware that the “on keys” works with array :slight_smile:

yes… N1QL is pretty powerful :muscle::slight_smile:
Check the syntax/docs, ON KEYS takes an expression.
Keep N1QL’ing… Cheers!!

1 Like