How to join more two document in same bucket?

NaramukAbus · April 18, 2018, 1:22pm

I need to join multiple document using n1ql query.
This is my document 1

{  "itemId": "item_6fdb36a8-60e1-4adc-a6f6-1ac07a30c66e",
    "item_category": {         
       "catName": "a"
    },
    "item_name": "b", 
    "type": "item",
    "_rev": "3-b07846b7433c2b245ade8089ea4fcb3e",
    "_id": "item_.1e790a80-a8b4-40a1-a0e3-585e29eee8a2"
  }

document 2

  "itemId": "item_2c1ee3af-2f63-408b-afe7-cab2dd933bcb",
    "item_category": {
       "catName": "c"
    },
    "item_name": "d", 
    "type": "item",
    "_rev": "3-b07846b7433c2b245ade8089ea4fcn9e",
    "_id": "item_.1e970a80-a6b4-40a1-a0e3-585e26eee8h2"
  }

document 3

  {
    "t_balance": 6.930000000000007,
    "t_date_time": 1523344440000,
    "t_discounts": [],
    "t_id": "0ad6f991-1abd-4560-a5cf-87d9053b414a",
    "t_invoice_no": "K058HG720",
    "t_items": [
      {
        "category": a,      
        "itemId": "item_6fdb36a8-60e1-4adc-a6f6-1ac07a30c66e",
        "name": "b-l",
        "priceVariations": [
          {
            "price": "12",
            "variationName": "Regular"
          },
          {
            "price": "12",
            "variationName": "s"
          },
          {
            "price": "24",
            "variationName": "l"
          }
        ],
        "selecteQty": 3,
        "selectedDiscount": {              
          "discountId": "discount_cd3359fd-43c2-446c-969c-350b7aaa0cdc",
          "discountName": "new year",
          "discountType": "Rs",
          "discountValue": 10
        },
      "selectedPrice": "62.00",
     "selectedVariation": {
      "price": "24",
      "variationName": "l"
     },      
     "type": "ITEM"
     },
      {
        "category": c,
        "itemId": "item_2c1ee3af-2f63-408b-afe7-cab2dd933bcb",
        "name": "d-Regular",
        "priceVariations": [
          {
            "price": "43",
            "variationName": "Regular"
          }
        ],
        "selecteQty": 1,
        "selectedDiscount": {
          "discountId": "discount_b3916581-7a1a-40e6-aaa1-9c0026240d3e",
          "discountName": "diwali",
          "discountType": "%",
          "discountValue": 4.5
        },
        "selectedPrice": "41.07",
        "selectedVariation": null,
        "type": "ITEM"
      }
    ],
    "t_value": 103.07,
    "type": "txn",
    "_rev": "3-665cf2397b8742b4bedd57557f6a1d67",
    "_id": "0ad6f991-1abd-4560-a5cf-87d9053b414a"
  }

document 1.itemId = document 3 . t_items. itemId
document 2.itemId = document 3 . t_items. itemId
i need to join document 1, 2 and document 3. I created query to join this documents. but i am getting error.

SELECT * FROM todo t JOIN todo s ON KEYS x.itemId UNNEST s.t_items AS x

error

Ambiguous reference to field x.

vsr1 · April 18, 2018, 1:42pm

It is rule based optimizations and Joins are from LEFT to RIGHT

SELECT *
FROM todo d3 UNNEST d3.t_items AS d3u
JOIN todo d1 ON KEYS d3u.itemId
WHERE …;

SELECT *
FROM todo d3
JOIN todo d1 ON KEYS ( ARRAY v.itemId FOR v IN d3.t_items END)
WHERE …;

In Q2 you are not processing array element you can use this also
SELECT *
FROM todo d3
JOIN todo d1 ON KEYS d3.t_items[*].itemId
WHERE …;

Depends on your projection representation you use right query.

NaramukAbus · April 19, 2018, 5:11am

Thank you @vsr1
It is working fine. But i am unable to get d1.item_name.
i need to get following values

d1.item_name
d3.name
sum of d3.selecteQty

i need to get results like
{
item_name : “b”,
selected_item_name : “b-l”,
sum_qty : “3”
},
{
item_name : “d”,
selected_item_name : “d-Regular”,
sum_qty : “1”
}

vsr1 · April 19, 2018, 12:05pm

SELECT d1._item_name, d3u.name, SUM(d3u. selecteQty) AS selecteQty
FROM todo d3 UNNEST d3.t_items AS d3u
JOIN todo d1 ON KEYS d3u.itemId
WHERE …
GROUP BY d1._item_name, d3u.name;

NaramukAbus · April 19, 2018, 1:37pm

Thank you @vsr1
I am unable to get d1.item_name . I tried many way to get but i am in same issue.

SELECT d1.item_name AS item, d3u.name AS name, SUM(d3u. selecteQty) AS selecteQty
FROM todo d3 UNNEST d3.t_items AS d3u
LEFT JOIN todo d1 ON KEYS d3u.itemId
GROUP BY d1.item_name, d3u.name;

i am getting results like this

[ 
 {
   "name": "d-Regular",
   "selecteQty": 1
 },
 {
   "name": "v-l",
   "selecteQty": 1
  }
]

vsr1 · April 19, 2018, 1:40pm

You using LEFT JOIN, if d1 is not present it will not project (MISSING documents/fields will not project)

NaramukAbus · April 20, 2018, 6:15am

Thank you @vsr1
for this query, do i want to create index? I created following index.

CREATE INDEX txn_itemId ON todo (itemId) WHERE type="transaction";

i am getting empty object with join query.
my query

SELECT  d3u.name AS name, SUM(d3u. selecteQty) AS selecteQty
FROM todo d3 UNNEST d3.t_items AS d3u
JOIN todo d1 ON KEYS d3u.itemId
GROUP BY  d3u.itemId,d3u.name;

results :
{
  "results": []
}

keshav_m · April 20, 2018, 6:46am

Without any predicates (filters) on type and itemId, this index won’t be used.

wrt results from the query, you should debug starting with simple (or *) projection and just the UNNEST and then JOIN and then add the group+sum.

NaramukAbus · April 20, 2018, 6:54am

Thank you @keshav_m
for this query i am getting empty objects.

SELECT *
FROM todo d3 UNNEST d3.t_items AS d3u
JOIN todo d1 ON KEYS d3u.itemId

keshav_m · April 20, 2018, 7:06am

What do you get for this?

SELECT *
FROM todo d3 UNNEST d3.t_items AS d3u

Since you have three types of document, it’s important to give the type for the starting keyspace.

SELECT *
    FROM todo d3 UNNEST d3.t_items AS d3u 
    WHERE d3.type = "txn"

NaramukAbus · April 20, 2018, 7:31am

Thanks @keshav_m. I am new to n1ql. i got d3’s all the documents without join. but i got empty results when i run the join query.

SELECT *
FROM todo d3 UNNEST d3.t_items AS d3u
JOIN todo d1 ON KEYS d3u.itemId
WHERE d3.type = "txn"  AND d1.type="item"

vsr1 · April 20, 2018, 12:14pm

INSERT INTO default VALUES("item_6fdb36a8-60e1-4adc-a6f6-1ac07a30c66e", {  "itemId": "item_6fdb36a8-60e1-4adc-a6f6-1ac07a30c66e", "item_category": {"catName": "a" }, "item_name": "b", "type": "item", "_rev": "3-b07846b7433c2b245ade8089ea4fcb3e", "_id": "item_.1e790a80-a8b4-40a1-a0e3-585e29eee8a2" });
INSERT INTO default VALUES( "item_2c1ee3af-2f63-408b-afe7-cab2dd933bcb", { "itemId": "item_2c1ee3af-2f63-408b-afe7-cab2dd933bcb", "item_category": { "catName": "c" }, "item_name": "d", "type": "item", "_rev": "3-b07846b7433c2b245ade8089ea4fcn9e", "_id": "item_.1e970a80-a6b4-40a1-a0e3-585e26eee8h2" });
INSERT INTO default VALUES("0ad6f991-1abd-4560-a5cf-87d9053b414a", { "t_balance": 6.930000000000007, "t_date_time": 1523344440000, "t_discounts": [], "t_id": "0ad6f991-1abd-4560-a5cf-87d9053b414a", "t_invoice_no": "K058HG720", "t_items": [ { "category": "a",      "itemId": "item_6fdb36a8-60e1-4adc-a6f6-1ac07a30c66e", "name": "b-l", "priceVariations": [ { "price": "12", "variationName": "Regular" }, { "price": "12", "variationName": "s" }, { "price": "24", "variationName": "l" } ], "selecteQty": 3, "selectedDiscount": {              "discountId": "discount_cd3359fd-43c2-446c-969c-350b7aaa0cdc", "discountName": "new year", "discountType": "Rs", "discountValue": 10 }, "selectedPrice": "62.00", "selectedVariation": { "price": "24", "variationName": "l" },      "type": "ITEM" }, { "category": "c", "itemId": "item_2c1ee3af-2f63-408b-afe7-cab2dd933bcb", "name": "d-Regular", "priceVariations": [ { "price": "43", "variationName": "Regular" } ], "selecteQty": 1, "selectedDiscount": { "discountId": "discount_b3916581-7a1a-40e6-aaa1-9c0026240d3e", "discountName": "diwali", "discountType": "%", "discountValue": 4.5 }, "selectedPrice": "41.07", "selectedVariation": null, "type": "ITEM" } ], "t_value": 103.07, "type": "txn", "_rev": "3-665cf2397b8742b4bedd57557f6a1d67", "_id": "0ad6f991-1abd-4560-a5cf-87d9053b414a" });

CREATE INDEX ix1 ON default(type);

SELECT  d3u.name AS name, SUM(d3u.selecteQty) AS selecteQty
FROM default d3 UNNEST d3.t_items AS d3u
JOIN default d1 ON KEYS d3u.itemId
WHERE d3.type = "txn" AND d1.type = "item"
GROUP BY  d3u.itemId,d3u.name;

Check out Designing Index for Query in Couchbase N1QL in http://blog.couchbase.com/wp-content/uploads/2017/03/N1QL-A-Practical-Guide-v2.pdf

vsr1 · April 20, 2018, 12:17pm

You can also use 5.5.0 Beta and ANSI JOIN https://blog.couchbase.com/ansi-join-support-n1ql/

NaramukAbus · April 20, 2018, 12:46pm

Thank you so much @vsr1 . Finally it is working fine.

Topic		Replies	Views
N1ql query for joining two types of document inside a single bucket SQL++	1	1221	May 31, 2018
How to join the same bucket? SQL++ n1ql	3	3686	October 20, 2015
Is it possible put Joins on multiple document types in Same bucket SQL++ n1ql	6	3433	November 27, 2015
Join 3 or more documents SQL++	2	999	October 13, 2017
Joins in the same bucket SQL++	1	1830	January 3, 2016

How to join more two document in same bucket?

Related topics