N1QL query to replace the product id with the product object that's contained in an array of object

SQL++
1

I have a cart document wherein it has a cartLineItems property which is an array of objects containing the product id and qty
I want to do a NEST statement and replace the product id with the product object and put the product id inside the product object

Cart Document
{
  "name": "Cart Name",
  "cartNumber": 12345,
  "cartLineItems": [
    {
      "product": "bluebonnet::product::078987a2-9675-433a-a7dc-c94bba880980",
      "qty": 56
    },
    {
      "product": "bluebonnet::product::07ca7b1e-bb1e-4834-955e-537d66f832da",
      "qty": 7
    },
  ]
}

Product Documents
{
  "active": false,
  "brand": "Acme",
  "category": "Grains",
  "department": "Books",
  "description": "Tasty Fresh Keyboard",
  "type": "product"
}

{
  "active": false,
  "brand": "Acme",
  "category": "Vitamin A",
  "department": "Kids",
  "description": "Ergonomic Fresh Gloves",
  "type": "product",
}

Expected Results

Cart Document
{
  "name": "Cart Name",
  "cartNumber": 12345,
  "cartLineItems": [
    {
      "product": {
        "id": "bluebonnet::product::078987a2-9675-433a-a7dc-c94bba880980",
        "brand": "Acme",
        "category": "Grains",
        "department": "Books",
        "description": "Tasty Fresh Keyboard",
      },
      "qty": 56
    },
    {
      "product": {
        "id": "bluebonnet::product::07ca7b1e-bb1e-4834-955e-537d66f832da",
        "active": false,
        "brand": "Acme",
        "category": "Vitamin A",
        "department": "Kids",
        "description": "Ergonomic Fresh Gloves",
        "type": "product",
      }
      "qty": 7
    },
  ]
}
2 
SELECT d.*,
       (SELECT c.qty, c.product AS id, p.*
        FROM d.cartLineItems AS c
        LEFT JOIN default AS p ON KEYS c.product) AS cartLineItems
FROM default AS d
WHERE d.name = "Cart Name";
3

Thanks for answering the question @vsr1!

I can confirm that the solution works! :slight_smile: