N1QL Join to array within a document

Interesting… Here are our key naming conventions. The “123456789” is the unique account #, of which there are thousands per client. So if customer_xyz has 10,000 accounts, we would have 10,000 account- documents, 10,000 financial- documents, and 10,000 charges- documents, all linked together by the account # at the end of the key. Hopefully that makes sense.

account-client_name-123456789
financial-client_name-123456789
charges-client_name-123456789

Here are some short examples of what the documents structure looks like:

account-customer_xyz-123456789
{
  "acctNumber": 123456789,
  "tenantName": "customer_xyz",
  "code": "166"
}

financial-customer_xyz-123456789
{
  "acctFinancials": [
    {
      "acctNumber": "19800001",
      "transactionId": "164126601",
      "transactionType": "Credit Adjustment",
      "amount": 30.10
    },
    {
      "acctNumber": "19800001",
      "transactionId": "162555860",
      "transactionType": "Credit Adjustment",
      "amount": 255.14
	}
  ],
  "tenantName": "customer_xyz",
  "acctNumber": "123456789"
}

charges-customer_xyz-123456789
{
  "acctCharges": [
    {
      "acctNumber": "123456789",
      "chargeId": "165532430",
      "amount": 1193.64
	},
	{
      "acctNumber": "123456789",
      "chargeId": "165247278",
      "amount": 1461.09
	}
  ],
  "tenantName": "customer_xyz",
  "acctNumber": "123456789"
}

Our end-goal in most circumstances will be to join the account doc to the detailed docs (financial or charges), then aggregate on a value within the nested financials or charges. So for a simple example, if I wanted to show total charges for all accounts I would need to join the “account-” docs to the “charges-” docs, doing a SUM() on the nested amounts inside the charges document.

Then if I want limit the results to only one tenant, would it be the most efficient to limit by providing only partial key names? E.g.:

FROM acctbucket accounts JOIN accountbucket charges
             ON KEYS ("account-customer_xyz-" || "charges-customer_xyz-");

To restrict it to ONLY ONE account

FROM acctbucket accounts USE KEYS ["account-customer_xyz-123456789" 
          JOIN accountbucket charges
             ON KEYS ("charges-customer_xyz-" || TOSTRING(accounts.acctNumber);

This is LOGICALLY (syntax is unsupported).

Please see: https://dzone.com/articles/join-faster-with-couchbase-index-joins

FROM acctbucket accounts USE KEYS ["account-customer_xyz-123456789" 
          JOIN accountbucket charges
 ON KEYS ("charges-customer_xyz-" || TOSTRING(accounts.acctNumber) = META(charges).id;

Sorry, I misunderstood the concatenation operator for a logical “or”. I’m having trouble getting this to work.

SELECT *
FROM acctbucket accounts USE KEYS "account-customer_xyz-123456789"
JOIN acctbucket financials ON KEYS "financials-customer_xyz-" || TOSTRING(accounts.acctNumber) = META(financials).id

It’s throwing an error saying “Ambiguous reference to field financials”, however I only have the two aliases, account and financials.

This is the actual syntax you should use. The key (“charges-customer_xyz-” || TOSTRING(accounts.acctNumber) is implictly joined with document key of charges (meta(charges).id).

FROM acctbucket accounts USE KEYS ["account-customer_xyz-123456789" 
   JOIN accountbucket charges
  ON KEYS ("charges-customer_xyz-" || TOSTRING(accounts.acctNumber);

Thanks keshav_m, that fixed it :slight_smile: Going back to the original question, is there a way (without specifying a single account number) to join between the account- and financial- docs to gain access to the nested elements within the financials doc (such as “amount”)?

To provide a simple example, we want to look at all accounts for customer_xyz with a code = 166, then join to the financials document, and SUM() the nested “amount” value, grouped by account number.

We’ve been able to successfully SUM() the financial amounts within the financials doc, but we have not been able to properly join it back to the account doc to allow for filtering by account level attributes (such as “code” for example).

account-customer_xyz-123456789
{
  "acctNumber": 123456789,
  "tenantName": "customer_xyz",
  "code": "166"
}

financial-customer_xyz-123456789
{
  "acctFinancials": [
    {
      "acctNumber": "123456789",
      "transactionId": "164126601",
      "transactionType": "Credit Adjustment",
      "amount": 30.10
    },
    {
      "acctNumber": "123456789",
      "transactionId": "162555860",
      "transactionType": "Credit Adjustment",
      "amount": 255.14
	}
  ],
  "tenantName": "customer_xyz",
  "acctNumber": "123456789"
}

If you don’t use the USE KEYS, the query will join ALL qualifying documents from accounts.
Because you have the field acctNumber in both accounts and chargers, you should have a type field for each document type (or change the field name).

FROM acctbucket accounts  
   JOIN accountbucket charges
  ON KEYS ("charges-customer_xyz-" || TOSTRING(accounts.acctNumber) 
 WHERE accounts.type = 'accounts' and charges.type = 'charges';