Filter nested documents

SQL++
1

Hi, I have a document structure that looks like this:

{
“id”: “livestock_0000F3785E BRALETTE_DEFECT”,
“cas”: 0,
“expiry”: 0,
“content”: {
“locationCode”: “DEFECT”,
“itemNo”: “0000F3785E BRALETTE”,
“quantity”: 3,
“locationType”: “LOST”,
“isEcommerceLocation”: false,
“colorQuantities”: [
{
“color”: “001/BLACK”,
“quantity”: 1,
“variantQuantities”: [
{
“variantCode”: “3”,
“quantity”: 1
}
]
},
{
“color”: “020/GREY HEATHE”,
“quantity”: 2,
“variantQuantities”: [
{
“variantCode”: “9”,
“quantity”: 2
}
]
}
]
},
“token”: null
}

It translates to: on location “DEFECT” there are 3 items in stock. 1 in color “001/BLACK” (this contains 1 variant with code 3) and 2 in color “020/GREY HEATHE” (the 2 variants both have code 9).

I’m fairly new to N1QL and I’m trying to retrieve the entire document (all 3 levels: location - color - variant) but filtered at the color level. Let’s say I want to filter by color “001/BLACK”, I want to retrieve something like this with a query:

{
“id”: “livestock_0000F3785E BRALETTE_DEFECT”,
“cas”: 0,
“expiry”: 0,
“content”: {
“locationCode”: “DEFECT”,
“itemNo”: “0000F3785E BRALETTE”,
“quantity”: 3,
“locationType”: “LOST”,
“isEcommerceLocation”: false,
“colorQuantities”: [
{
“color”: “001/BLACK”,
“quantity”: 1,
“variantQuantities”: [
{
“variantCode”: “3”,
“quantity”: 1
}
]
}
]
},
“token”: null
}

This is probably a pretty simple thing to do but I’ve tried correlated subqueries, nesting, unnesting, joining and can’t seem to figure this one out… Can somebody help / guide me please?

Thanks!

1 Like
2

You can reconstruct colorQuantities as follows

ARRAY v FOR v IN colorQuantities WHEN v.color IN ["001/BLACK"] END AS colorQuantities

Example: SELECT d. locationCode, d.itemNo, d.quantity,ARRAY v FOR v IN d.colorQuantities WHEN v.color IN ["001/BLACK"] END AS colorQuantities FROM default d;
1 Like
4

Perfect, you nailed it! I’ve been trying to achieve this for hours but you got me to the result I needed. Thank you very much! :slight_smile: