Join on field in array of objects

SQL++
,
1

I have this cart document

{
  "_id": "UserCart::123",
  "_type": "UserCart",
  "cartItems": [
    {
      "addedAt": 1473404780942,
      "price": 750,
      "itemId": "I125",
    },
    {
      "addedAt": 1473404780942,
      "price": 840,
      "itemId": "I652",
    },
    {
      "addedAt": 1473404780942,
      "price": 360,
      "itemId": "I981",
    }
  ],
  "userId": "Hkr5io0j57d12b8c"
}

Now I have this Item Doc, I am cutting doc out for convenience

I125

   {
        "id": "I125",
        "name": "An ice sculpture",
        "price": 750,
        "tags": ["cold", "ice"],
        "dimensions": {
            "length": 7.0,
            "width": 12.0,
            "height": 9.5
        },
        "warehouseLocation": {
            "latitude": -78.75,
            "longitude": 20.4
        },
       "images":['xyz.com/hgfyfd.jpg','xyz.com/fsasf.jpg'],
       "seller": '841'
    }

now when I get user cart I want data like this:

{
      "_id": "UserCart::123",
      "_type": "UserCart",
      "cartItems": [
        {
          "addedAt": 1473404780942,
          "price": 750,
          "itemId": "I125",
          "item":{
            "id": "I125",
            "name": "An ice sculpture",
            "price": 750,
            "tags": ["cold", "ice"],
            "dimensions": {
                "length": 7.0,
                "width": 12.0,
                "height": 9.5
            },
            "warehouseLocation": {
                "latitude": -78.75,
                "longitude": 20.4
            },
           "images":['xyz.com/hgfyfd.jpg','xyz.com/fsasf.jpg'],
           "seller": '841'
          }
        },
        {
          "addedAt": 1473404780942,
          "price": 840,
          "itemId": "I652",
          "item":{
            "id": "I125",
            "name": "An ice sculpture",
            "price": 750,
            "tags": ["cold", "ice"],
            "dimensions": {
                "length": 7.0,
                "width": 12.0,
                "height": 9.5
            },
            "warehouseLocation": {
                "latitude": -78.75,
                "longitude": 20.4
            },
           "images":['xyz.com/hgfyfd.jpg','xyz.com/fsasf.jpg'],
           "seller": '841'
          }
        },
        {
          "addedAt": 1473404780942,
          "price": 360,
          "itemId": "I981",
          "item":{
            "id": "I125",
            "name": "An ice sculpture",
            "price": 750,
            "tags": ["cold", "ice"],
            "dimensions": {
                "length": 7.0,
                "width": 12.0,
                "height": 9.5
            },
            "warehouseLocation": {
                "latitude": -78.75,
                "longitude": 20.4
            },
           "images":['xyz.com/hgfyfd.jpg','xyz.com/fsasf.jpg'],
           "seller": '841'
          }
        }
      ],
      "userId": "Hkr5io0j57d12b8c"
    }

I have tried with join but that gives me item object as a separate object but i want it as whole cartitem object

2

select cart.*,cartItems FROM default cart NEST default cartItems ON KEYS cart.cartItems[*].itemId WHERE cart._type = “UserCart”;

If you need all carts with no matching entries also try this.

select cart.*,cartItems FROM default cart LEFT NEST default cartItems ON KEYS cart.cartItems[*].itemId WHERE cart._type = “UserCart”;

3

@vsr1

I have already tried this but this will not work as this will override existing cartItem parameters with whole item object like this

{
      "_id": "UserCart::123",
      "_type": "UserCart",
      "cartItems": [
        {  
	    "id": "I125",
            "name": "An ice sculpture",
            "price": 750,
            "tags": ["cold", "ice"],
            "dimensions": {
                "length": 7.0,
                "width": 12.0,
                "height": 9.5
            },
            "warehouseLocation": {
                "latitude": -78.75,
                "longitude": 20.4
            },
           "images":['xyz.com/hgfyfd.jpg','xyz.com/fsasf.jpg'],
           "seller": '841'
        }
      ],
      "userId": "Hkr5io0j57d12b8c"
    }

and I need existing parameter with whole item object.

4

select cart.*, array_agg( object_add(cartItems,“item”,IFNULL(items,MISSING)) ) cartItems FROM default cart UNNEST cart.cartItems LEFT JOIN default items ON KEYS cartItems.itemId WHERE cart._type = “UserCart” group by cart;

5

You can also try this.

select cart.*,cartItems FROM default cart LEFT NEST default cartItems ON KEYS cart.cartItems[*].itemId
LET cartItems = ARRAY object_add(cv,“item”,IFNULL(FIRST iv FOR iv IN cartItems WHEN iv.id = cv.itemId END, MISSING)) FOR cv IN cart.cartItems END
WHERE cart._type = “UserCart”;

6

object_add function added in CB 4.5 and i want to implement it in CB 4.1 @vsr1

7

select cart.*,cartItems FROM default cart LEFT NEST default cartItems ON KEYS cart.cartItems[*].itemId
LET cartItems = ARRAY {“addedAt”:cv.addedAt, “price”:cv.price, “itemId”: cv.itemId,“item”:IFNULL(FIRST iv FOR iv IN cartItems WHEN iv.id = cv.itemId END, MISSING)} FOR cv IN cart.cartItems END
WHERE cart._type = “UserCart”;

Try this. If this did not help you should consider CB 4.5

8

@vsr1 same result as previous thanx for help though

9

@geraldss @prasad If you can help with this

10

Can you use 4.5 to test the solution from @vsr1.

11

This is output from 4.1.2. Is this what you are expecting.

  insert into default values("k01", { "_id": "UserCart::123", "_type": "UserCart", "cartItems": [ { "addedAt": 1473404780942, "price": 750, "itemId": "I125" }, { "addedAt": 1473404780942, "price": 840, "itemId": "I652" }, { "addedAt": 1473404780942, "price": 360, "itemId": "I981" } ], "userId": "Hkr5io0j57d12b8c" });
 insert into default values("I125",{ "id": "I125", "name": "An ice sculpture", "price": 750, "tags": ["cold", "ice"], "dimensions": { "length": 7.0, "width": 12.0, "height": 9.5 }, "warehouseLocation": { "latitude": -78.75, "longitude": 20.4 }, "images":['xyz.com/hgfyfd.jpg','xyz.com/fsasf.jpg'], "seller": '841' });
 insert into default values("I652",{ "id": "I652", "name": "An ice sculpture", "price": 750, "tags": ["cold", "ice"], "dimensions": { "length": 7.0, "width": 12.0, "height": 9.5 }, "warehouseLocation": { "latitude": -78.75, "longitude": 20.4 }, "images":['xyz.com/hgfyfd.jpg','xyz.com/fsasf.jpg'], "seller": '841' });

select cart._type, cart._id, citems cartItems FROM default cart LEFT NEST default cartItems ON KEYS cart.cartItems[*].itemId LET citems = ARRAY {"addedAt":cv.addedAt, "price":cv.price, "itemId": cv.itemId,  "item":IFNULL(FIRST iv FOR iv IN cartItems WHEN iv.id = cv.itemId END, MISSING)} FOR cv IN cart.cartItems END  WHERE cart._type = "UserCart";
{
    "requestID": "75c8489c-48aa-4538-922f-6b50d6d7fa69",
    "signature": {
        "_id": "json",
        "_type": "json",
        "cartItems": "json"
    },
    "results": [
        {
            "_id": "UserCart::123",
            "_type": "UserCart",
            "cartItems": [
                {
                    "addedAt": 1473404780942,
                    "item": {
                        "dimensions": {
                            "height": 9.5,
                            "length": 7,
                            "width": 12
                        },
                        "id": "I125",
                        "images": [
                            "xyz.com/hgfyfd.jpg",
                            "xyz.com/fsasf.jpg"
                        ],
                        "name": "An ice sculpture",
                        "price": 750,
                        "seller": "841",
                        "tags": [
                            "cold",
                            "ice"
                        ],
                        "warehouseLocation": {
                            "latitude": -78.75,
                            "longitude": 20.4
                        }
                    },
                    "itemId": "I125",
                    "price": 750
                },
                {
                    "addedAt": 1473404780942,
                    "item": {
                        "dimensions": {
                            "height": 9.5,
                            "length": 7,
                            "width": 12
                        },
                        "id": "I652",
                        "images": [
                            "xyz.com/hgfyfd.jpg",
                            "xyz.com/fsasf.jpg"
                        ],
                        "name": "An ice sculpture",
                        "price": 750,
                        "seller": "841",
                        "tags": [
                            "cold",
                            "ice"
                        ],
                        "warehouseLocation": {
                            "latitude": -78.75,
                            "longitude": 20.4
                        }
                    },
                    "itemId": "I652",
                    "price": 840
                },
                {
                    "addedAt": 1473404780942,
                    "itemId": "I981",
                    "price": 360
                }
            ]
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "17.138372ms",
        "executionTime": "17.030393ms",
        "resultCount": 1,
        "resultSize": 2326
    }
}
2 Likes
12

yeah perhaps i did something wrong i ll check it again