N1QL Multiple join and sum query producing wrong output

Question

I have a bucket containing the following three documents:

coffee {
    id
}

cold_coffee {
    coffeeId
    number
}

warm_coffee {
    coffeeId
    number
}

I have written the following query in N1QL using Couchbase Server Community Edition 6.0.0:

SELECT META(coffee).id as coffeeId,
SUM(cold_coffee.`number`) as `ccNumber`,
SUM(warm_coffee.`number`) as `wcNumber`,
FROM coffee_bucket coffee
left JOIN coffee_bucket cold_coffee
    ON  META(coffee).id = cold_coffee.coffeeId 
    and cold_coffee.type='cold_coffee'
left JOIN coffee_bucket warm_coffee
    ON META(coffee).id = warm_coffee.coffeeId 
    and warm_coffee.type='warm_coffee'
where coffee.type='coffee'
group by META(coffee).id;

I have multiple cold_coffee and warm_coffee documents for every coffee and I need to sum the numbers for all of the cold_coffee and warm_coffee documents. The problem I am having is that if for example I have the following documents:

[
    coffee {
        id: 1
    },
    cold_coffee {
        coffeeId:1
        number:5 
    },
    cold_coffee {
        coffeeId:1
        number:5 
    },
    warm_coffee {
        coffeeId:1
        number:10
    }
]

My totals are the following:

ccNumber: 10
wcNumber:20

It seems because of the joins the single warm_coffee document is being counted twice?

I came across this site with possibly the same error but unfortunately it's SQL.

And I am unsure how to solve this using N1QL because the right hand term of a JOIN must be a table / bucket as seen in the following post.

Here is a possible solution but I am not sure how to implement this is N1QL?

Can someone please assist?

vsr · Accepted Answer · 2019-10-31 19:11:54Z

3

JOINS can expand the original rows. During first Join left most document is expanded. You are using first document field as second join condition that can produce multiple documents same condition. This how semantics works. You need to adjust your join condition based on the needs.

The second JOIN uses one document from the LEFT most. SUM over DISTINCT may not work because different documents same value (warm_coffee, may under count) is counted once. If I am right you are looking second join to use unique document from LEFT most coffee.

May be you are looking some thing like this

SELECT c.coffeeId,
       MAX(c.ccNumber) AS `ccNumber`,
       SUM(warm_coffee.`number`) AS `wcNumber`,
FROM ( SELECT META(coffee).id AS coffeeId,
       SUM(cold_coffee.`number`) AS `ccNumber`
       FROM coffee_bucket coffee
       LEFT JOIN coffee_bucket cold_coffee
            ON  META(coffee).id = cold_coffee.coffeeId AND cold_coffee.type='cold_coffee'
       WHERE coffee.type='coffee'
       GROUP BY META(coffee).id
     ) AS c
LEFT JOIN coffee_bucket warm_coffee
          ON c.coffeeId = warm_coffee.coffeeId AND warm_coffee.type='warm_coffee'
GROUP BY c.coffeeId;

3 level join

SELECT d.coffeeId,
       MAX(c.ccNumber) AS `ccNumber`,
       MAX(c.wcNumber) AS `wcNumber`,
       SUM(ch.`number`) AS `chNumber`
FROM ( SELECT c.coffeeId,
              MAX(c.ccNumber) AS `ccNumber`,
              SUM(warm_coffee.`number`) AS `wcNumber`,
       FROM ( SELECT META(coffee).id AS coffeeId,
              SUM(cold_coffee.`number`) AS `ccNumber`
              FROM coffee_bucket coffee
              LEFT JOIN coffee_bucket cold_coffee
                   ON  META(coffee).id = cold_coffee.coffeeId AND cold_coffee.type='cold_coffee'
              WHERE coffee.type='coffee'
              GROUP BY META(coffee).id
            ) AS c
       LEFT JOIN coffee_bucket warm_coffee
                 ON c.coffeeId = warm_coffee.coffeeId AND warm_coffee.type='warm_coffee'
       GROUP BY c.coffeeId) AS d
LEFT JOIN coffee_bucket ch
     ON d.coffeeId = ch.coffeeId AND ch.type='chaoc_coffee'
GROUP BY d.coffeeId
;

edited Oct 31, 2019 at 19:11

answered Oct 30, 2019 at 13:57

vsr

7,4441 gold badge13 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Johan Ferreira Over a year ago

Great! Thank you very much for your help! Could you please give advice if for example another document like choc_coffee was added and a sum was required for all of those documents? I am struggling to understand how to add that logic to your solution? Thanks!

Johan Ferreira Over a year ago

Btw this code is working if a sum is required for those two documents

vsr Over a year ago

Check out 3 level join. Correlated subquery requires use keys. Otherwise it make easier with correlated subquery

Collectives™ on Stack Overflow

N1QL Multiple join and sum query producing wrong output

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related