Join one document's array into another document's array placeholder

SQL++
1

Hi,
There are similar topics to this question (for example, How to map array values in one document to another and display in result) that I have looked at, but the syntax on these is pretty tricky. If someone has the time to look at this, it would be greatly appreciated.

I have two documents, a productCatalog and baseOptions. I would like to leave an empty array placeholder for the baseOptions inside the productCatalog document and then fill it in with the baseOptions when retrieving the catalog. It seems that I should be able to accomplish this with, for example, the array_agg, but so far no luck.
Here is the productCatalog document:
{
“documentType”: “productCatalogTest”,
“offerings”: [{
“id”: “1”,
“name”: “consumer”,
“description”: “Consumer”,
“type”: “b2c”,
“startDateTimeInclusive”: “2020-06-30T10:07:23Z”,
“endDateTimeInclusive”: “2025-06-30T10:07:23Z”,
“lifecycleStatus”: “Active”,
“productPackages”: [{
“id”: “1”,
“name”: “consumer product”,
“description”: “consumer product”,
“version”: “1”,
“startDateTimeInclusive”: “2020-06-30T10:07:23Z”,
“endDateTimeInclusive”: “2025-06-30T10:07:23Z”,
“lifecycleStatus”: “Active”,
“baseOptions”: “consumerOptionsArrayRef”
}]
}, {
“id”: “2”,
“name”: “business”,
“description”: “Business”,
“type”: “b2b”,
“startDateTimeInclusive”: “2020-06-30T10:07:23Z”,
“endDateTimeInclusive”: “2025-06-30T10:07:23Z”,
“lifecycleStatus”: “Active”,
“productPackages”: [{
“id”: “1”,
“name”: “business product”,
“description”: “business product”,
“version”: “1”,
“startDateTimeInclusive”: “2020-06-30T10:07:23Z”,
“endDateTimeInclusive”: “2025-06-30T10:07:23Z”,
“lifecycleStatus”: “Active”,
“baseOptions”: “businessOptionsArrayRef”
}]
}]
}
I would like to join this document’s consumerOptions array into the productCatalog array:
{
“documentType”: “baseOptions”,
“version”: 1,
“consumerOptions”: [{
“id”: “39”,
“value”: “ENABLED”,
“name”: “Base option 1 v1”,
“disabled”: true,
“provisionable”: false,
“billable”: true,
“startDateTimeInclusive”: “2020-06-30T10:07:23Z”,
“endDateTimeInclusive”: “2025-06-30T10:07:23Z”
}, {
“id”: “40”,
“value”: “ENABLED”,
“name”: “Base option 2 v1”,
“billable”: true,
“startDateTimeInclusive”: “2020-06-30T10:07:23Z”,
“endDateTimeInclusive”: “2025-06-30T10:07:23Z”
}, {
“id”: “41”,
“value”: “ENABLED”,
“name”: “Base option 3 v1”,
“billable”: false,
“startDateTimeInclusive”: “2020-06-30T10:07:23Z”,
“endDateTimeInclusive”: “2025-06-30T10:07:23Z”
}, {
“id”: “42”,
“value”: “ENABLED”,
“name”: “Base option 4 v1”,
“billable”: false,
“startDateTimeInclusive”: “2020-06-30T10:07:23Z”,
“endDateTimeInclusive”: “2025-06-30T10:07:23Z”
}, {
“id”: “43”,
“value”: “DISABLED”,
“name”: “Base option 5 v1”,
“billable”: true,
“startDateTimeInclusive”: “2020-06-30T10:07:23Z”,
“endDateTimeInclusive”: “2025-06-30T10:07:23Z”
}]
}

2 
WITH baseoption AS ((SELECT b.* FROM mybucket AS b WHERE b.documentType = "baseOptions")[0])
SELECT p.*, ARRAY (ARRAY pp FOR OBJECT_PUT(pp, "baseOptions", (FIRST co FOR co IN  baseoption. consumerOptions WHEN co.id = pp. baseOptions END)) IN o.productPackages END) o FOR o IN p.offerings END AS offerings
FROM mybucket AS p
WHERE p.documentType = "productCatalogTest";

OR

WITH baseoptionObj AS ((SELECT RAW OBJECT TO_STR(v.id):v FOR v IN b.consumerOptions END 
                        FROM mybucket AS b 
                        WHERE b.documentType = "baseOptions")[0])
SELECT p.*, ARRAY (ARRAY pp FOR OBJECT_PUT(pp, "baseOptions", baseoptionObj.[TO_STR(pp. baseOptions)]) IN o.productPackages END) o FOR o IN p.offerings END AS offerings
FROM mybucket AS p
WHERE p.documentType = "productCatalogTest";
3

Hey!
Thanks for the input! Neither of these runs as-is, but I can use this a base for continuing to look at this problem.