How to do left joins by checking if a product id matches one of the product ids present on the array of sub document of our invoice document

SQL++
, ,
1

Hi,

I would like to ask how to do left joins
by checking if a product id matches one of the product ids present on the array of sub document of our invoice document.

So i have a list product of documents

// Product documents
[
  {
    id: 'product::01',
    name: Product 1,
  },
  {
    id: 'product::02',
    name: Product 2,
  },
  {
    id: 'product::03',
    name: Product 3,
  }
]

I also have an invoice document which contains invoice items which is an array of product documents

// invoice document
{
  id: 'invoice:01',
  invoiceNumber: 'inv-01',
  invoiceItems: [
    {
      {
        id: 'product::01',
        name: Product 1,
      },
      {
        id: 'product::02',
        name: Product 2,
      }
    }
  ]  
}

My expected result is something like this

// Product documents
[
  {
    id: 'product::01',
    name: Product 1,
    id: 'invoice:01',
    invoiceNumber: 'inv-01',
  },
  {
    id: 'product::02',
    name: Product 2,
    id: 'invoice:01',
    invoiceNumber: 'inv-01',
  },
  {
    id: 'product::03',
    name: Product 3,
  }
]

Here’s the query that I made but this doesn’t seem to work :frowning:

SELECT meta(products).id, meta(invoices).id
FROM bucket products
LEFT JOIN bucket invoices
ON meta(invoices).id = ‘invoice::01’
AND ANY invoiceItem IN invoices.invoiceItems SATISFIES invoiceItem.id= meta(products).id END
WHERE products.type = “product” LIMIT 50

2

Example 12 https://blog.couchbase.com/ansi-join-support-n1ql/

SELECT  META(p).id, p.name, META(i).id  AS invid, i.invoiceNumber
FROM  `bucket`   AS p
LEFT JOIN  `bucket`   AS i
ON  ANY iv IN i.invoiceItems SATISFIES iv.id = META(p).id 
WHERE p.type = "product" LIMIT 50
3

Thank you for the response.

I may have forgotten to mention that I need to join to a specific invoice document and not all the invoice document that’s why I had this query.

LEFT JOIN bucket invoices
ON meta(invoices).id = ‘invoice::01’

4 
SELECT  META(p).id, p.name, META(i).id  AS invid, i.invoiceNumber
FROM  `bucket`   AS p
LEFT JOIN  `bucket`   AS i USE KEYS "invoice::01"
ON  ANY iv IN i.invoiceItems SATISFIES iv.id = META(p).id 
WHERE p.type = "product" LIMIT 50

OR

SELECT  META(p).id, p.name,  
                    CASE WHEN META(p).id IN invoice.pids THEN {invoice.invid, invoice.invoiceNumber} END.*
FROM  `bucket`   AS p
LET invoice = (SELECT  i.invoiceItems[*].id  AS pids, META(i).id AS invid, i.invoiceNumber
                                FROM  `bucket` AS i USE KEYS "invoice::01")[0]
WHERE p.type = "product"  LIMIT 50
1 Like
5

Cool… Thank you so much! I’ll try this out.!

Uhhm, I was able to come up with another solution for my question while waiting for a response.
I’d like to ask what’s your opinion on this if this is also a good solution.

SELECT meta(products).id, 
CASE WHEN (ANY invoiceItem IN `invoice`.invoiceItems SATISFIES invoiceItem.id = meta(`products`).id END) = true then invoice else null END as invoice
FROM `bucket` `products`
LET invoice = (select invoices.* from `bucket` `invoices` USE KEYS 'invoice::01')[0]
WHERE products.type = 'product' LIMIT 50
6

You can use that so that and avoid join

1 Like
7

Thanks @vsr1,

In this scenario, is it more optimal to use the LET statement than a LEFT JOIN statement?