Need Join for 2 documents based on fields

SQL++
1

I am using
Hi,
I am using Version: 4.5.0-2601 Enterprise Edition (build-2601) of couchbase
I am not able to get a query with join.
I have a document with van info which contain orderid.
Van1
{
“VehicleTrip”: {
“departureTime”: 1470117600,
“storeNumber”: 5031,
“cdaName”: “MatthewJohnston”,
“vanType”: “DOTCOM_VAN_DELIVERY”,
“isCDAExtraHelpPromptNeeded”: true,
“vehicleTripId”: “20112”,
“friendlyVanName”: “5J”,
“cdaBadge”: “368”,

},
“Orders”: {
“Order”: [
{
“orderId”: “114156699”,
}
]
}

Below are 2 order documents
Order1
{
“OrderDetails”: {
“orderType”: “Delivery”,
“isCorelineSubstituted”: “N”,
“orderId”: “114156699”,
“friendlyVanName”: “5J”,
“EPurseName”: " Gift Card",
“orderStatus”: “Checked Out”,
“voucherPromotionName”: “Computers for Schools”,
“customerNumber”: “14463097”,
“storeId”: “5031”,
“customerSupportNumber”: “0800 323 4040”,
“shortOrderNumber”: “4691”,
“isStoredOrder”: false,
“deliveryDate”: “08/01/2016”,
“VoucherName”: “Clubcard Voucher”
}
}
order2

{
“OrderDetails”: {
“orderType”: “Delivery”,
“isCorelineSubstituted”: “N”,
“orderId”: “114156723”,
“friendlyVanName”: “5J”,
“EPurseName”: " Gift Card",
“orderStatus”: “Checked Out”,
“voucherPromotionName”: “Computers for Schools”,
“customerNumber”: “14463097”,
“storeId”: “5031”,
“customerSupportNumber”: “0800 323 4040”,
“shortOrderNumber”: “4691”,
“isStoredOrder”: false,
“deliveryDate”: “08/01/2016”,
“VoucherName”: “Clubcard Voucher”
}
}

I want a join query for van1 and order where van1.orderid = order.orderid
I want data of both van and the order data which matches van1.orderid = order.orderid

I tried with

select b., d. from Delivery.Orders b Join Delivery.OrderDetails d
ON KEYS “orderId” || TOSTRING(b.orderId) where b.orderId = "114156699"
but got empty response

please help

2

This part of Data might not a valid JSON format.
do you mean:

"Orders": { "Order": [ { "orderId": "114156699"} ]}

?
and the Order1 document with documentKey(id) == 114156699 and Order2 document with documentKey(id) == 114156723 ?

If so,Try this:

SELECT o.*,p.* 
FROM default o JOIN default p ON KEYS ARRAY object_values(x)[0] FOR x IN o.Orders.`Order` END
3

Hi Yang,

I corrected the missing ] .

I tried with your query by replacing my bucket name as Delivery, but got empty response.

SELECT o.,p.
FROM Delivery o JOIN Delivery p ON KEYS ARRAY object_values(x)[0] FOR x IN o.Orders.Order END

Is there anything wrong.

4

order is Reserved keyword in N1QL,I think you should escape by `

5 
 @vinayj4u Please use the following recommendation below and let us know how your query/performance is working now. Thanks.   

 INSERT INTO van values("van1",
    {
    "VehicleTrip": {
    "departureTime": 1470117600,
    "storeNumber": 5031,
    "cdaName": "MatthewJohnston",
    "vanType": "DOTCOM_VAN_DELIVERY",
    "isCDAExtraHelpPromptNeeded": true,
    "vehicleTripId": "20112",
    "friendlyVanName": "5J",
    "cdaBadge": "368"
    },
    "Orders": {
    "Order": [
    {
    "orderId": "114156699"
    }
    ]
    }
    }
    );

    insert into orders values("orders::114156699",
    {
    "OrderDetails": {
    "orderType": "Delivery",
    "isCorelineSubstituted": "N",
    "orderId": "114156699",
    "friendlyVanName": "5J",
    "EPurseName": " Gift Card",
    "orderStatus": "Checked Out",
    "voucherPromotionName": "Computers for Schools",
    "customerNumber": "14463097",
    "storeId": "5031",
    "customerSupportNumber": "0800 323 4040",
    "shortOrderNumber": "4691",
    "isStoredOrder": false,
    "deliveryDate": "08/01/2016",
    "VoucherName": "Clubcard Voucher"
    }
    }
    );


    insert into orders values("orders::114156723",
    {
    "OrderDetails": {
    "orderType": "Delivery",
    "isCorelineSubstituted": "N",
    "orderId": "114156723",
    "friendlyVanName": "5J",
    "EPurseName": " Gift Card",
    "orderStatus": "Checked Out",
    "voucherPromotionName": "Computers for Schools",
    "customerNumber": "14463097",
    "storeId": "5031",
    "customerSupportNumber": "0800 323 4040",
    "shortOrderNumber": "4691",
    "isStoredOrder": false,
    "deliveryDate": "08/01/2016",
    "VoucherName": "Clubcard Voucher"
    }
    }
    );


    create primary index on orders;
    create primary index on van;

    select o from van unnest
                      Orders.`Order` as oid
                          inner join
                              orders o on keys "orders::" || oid.orderId;

    Results:
    [
      {
        "o": {
          "OrderDetails": {
            "EPurseName": " Gift Card",
            "VoucherName": "Clubcard Voucher",
            "customerNumber": "14463097",
            "customerSupportNumber": "0800 323 4040",
            "deliveryDate": "08/01/2016",
            "friendlyVanName": "5J",
            "isCorelineSubstituted": "N",
            "isStoredOrder": false,
            "orderId": "114156699",
            "orderStatus": "Checked Out",
            "orderType": "Delivery",
            "shortOrderNumber": "4691",
            "storeId": "5031",
            "voucherPromotionName": "Computers for Schools"
          }
        }
      }
    ]

    More Optimal version -- Remember to use the variable v in your queries to exploit the index:

    create index vanoid on van(ALL DISTINCT ARRAY v for v in Orders.`Order` END);

    select o from van unnest
                      Orders.`Order` as v
                          inner join
                              orders o on keys "orders::" || v.orderId
    WHERE v.orderId = "114156699";

    Results:
    [
      {
        "o": {
          "OrderDetails": {
            "EPurseName": " Gift Card",
            "VoucherName": "Clubcard Voucher",
            "customerNumber": "14463097",
            "customerSupportNumber": "0800 323 4040",
            "deliveryDate": "08/01/2016",
            "friendlyVanName": "5J",
            "isCorelineSubstituted": "N",
            "isStoredOrder": false,
            "orderId": "114156699",
            "orderStatus": "Checked Out",
            "orderType": "Delivery",
            "shortOrderNumber": "4691",
            "storeId": "5031",
            "voucherPromotionName": "Computers for Schools"
          }
        }
      }
    ]


    Explain: 

    [
      {
        "plan": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "DistinctScan",
              "scan": {
                "#operator": "IndexScan",
                "index": "vanoid",
                "index_id": "c2e95abab7d4ed24",
                "keyspace": "van",
                "namespace": "default",
                "spans": [
                  {
                    "Range": {
                      "High": [
                        "\"114156699\""
                      ],
                      "Inclusion": 3,
                      "Low": [
                        "\"114156699\""
                      ]
                    }
                  }
                ],
                "using": "gsi"
              }
            },
            {
              "#operator": "Parallel",
              "~child": {
                "#operator": "Sequence",
                "~children": [
                  {
                    "#operator": "Fetch",
                    "keyspace": "van",
                    "namespace": "default"
                  },
                  {
                    "#operator": "Unnest",
                    "as": "v",
                    "expr": "((`van`.`Orders`).`Order`)"
                  },
                  {
                    "#operator": "Join",
                    "as": "o",
                    "keyspace": "orders",
                    "namespace": "default",
                    "on_keys": "(\"orders::\" || (`v`.`orderId`))"
                  },
                  {
                    "#operator": "Filter",
                    "condition": "((`v`.`orderId`) = \"114156699\")"
                  },
                  {
                    "#operator": "InitialProject",
                    "result_terms": [
                      {
                        "expr": "`o`"
                      }
                    ]
                  },
                  {
                    "#operator": "FinalProject"
                  }
                ]
              }
            }
          ]
        },
        "text": "select o from van unnest\n                  Orders.`Order` as v\n                      inner join\n                          orders o on keys \"orders::\" || v.orderId\nWHERE v.orderId = \"114156699\";"
      }
    ]