N1QL join on keys and array

SQL++
1

I have 2 simple documents which I am trying to join but for some reason this query does not return any results. The syntax is correct but unfortunately it is not returning any data. I am trying to get the query under DOES NOT WORK to return results for me.

DOES NOT WORK
select ARRAY v FOR v IN borrow.borrowers WHEN v.members is not null END AS borrowers
FROM bkt_cpblending_dev lending
JOIN bkt_cpblending_dev borrow
ON KEYS (lending.applicationId || ‘-BORROWERS’)
where lending.class = ‘com.citi.pb.lending.entity.LendingApplication’
AND borrow.class = ‘com.citi.pb.lending.entity.collateral.Borrowers’****
AND ANY v IN borrow.borrowers SATISFIES v.members is not null END

WORKS
select ARRAY v FOR v IN borrow.borrowers WHEN v.members is not null END AS borrowers
FROM bkt_cpblending_dev lending
JOIN bkt_cpblending_dev borrow
ON KEYS (‘LENDING-MSBF-12345-BORROWERS’)
where lending._class = ‘com.citi.pb.lending.entity.LendingApplication’
AND borrow._class = ‘com.citi.pb.lending.entity.collateral.Borrowers’
AND ANY v IN borrow.borrowers SATISFIES v.members is not null END

----------------------DATA SETUP---------------------------------------

upsert into bkt_cpblending_dev (KEY, VALUE) VALUES (‘LENDING-MSBF-12345-BORROWERS’,
{
“_class”: “com.citi.pb.lending.entity.collateral.Borrowers”,
“borrowers”: [
{
“address”: " ADR_LINE",
“city”: “ADR_CITY_NM”,
“doddFrankInd”: “Not Enrolled”,
“egTitle”: “FST_NM”,
“egType”: “Individual”,
“entlKey”: “12345”,
“entlNbr”: “0272”,
“isSelected”: true,
“isSelectedForAddress”: false,
“marginUtilInd”: “NA”,
“members”: [
{
“addresses”: [
{
“address”: “ADR_LINE”,
“adrKey”: “10400000068184”,
“city”: “ADR_CITY”,
“ctry”: “US”,
“isSelectedForAddress”: true,
“zipcode”: “12345”
}
]
}
]

}
]

}
)

upsert into bkt_cpblending_dev (KEY, VALUE) VALUES (‘LENDING-MSBF-12345’,
{
“_class”: “com.citi.pb.lending.entity.LendingApplication”,
“applicationAcceptedDt”: 1536714611573,
“applicationApprovedDt”: 1536715700014,
“applicationDate”: 1536714417819,
“controlPerson”: false,
“crossCollateralization”: false,
“eSignFlag”: true,
“isNonSTP”: false,
“lastModification”: 1536718036164,
“lastModifiedBy”: “auditor”,
“purposeId”: “1003”,
“screens”: {
“screensCovered”: {
“ACCOUNTS_SELECTION”: true,
“ALL”: false,
“BORROWEREG_SELECTION”: true,
“CONTACTDETAILS_SELECTTION”: true,
“DASHBOARD”: false,
“FINAL_SELECTION”: true,
“REVIEWSCREEN_SELECTION”: true
}
},
“solicitedTransaction”: false,
“status”: “DOCUMENTS_BEING_PREPARED”,
“structuredRequest”: false
}
)

2

ON KEYS (lending.applicationId || ‘-BORROWERS’)

In your leading document you don’t have applicationId field so this expression evaluates MISSING and JOIN doesn’t qualify any results.

Based on your example (borrowers document key prefix is leading document key) you can try this

ON KEYS (META(lending).id || ‘-BORROWERS’)

3

That works. Thank you so much.

4

I now have a requirement to return results even if the data is missing in one of the documents.

Eg:
select ARRAY v FOR v IN borrow.borrowers WHEN v.members is not null END AS borrowers
FROM bkt_cpblending_dev lending
JOIN bkt_cpblending_dev borrow
ON KEYS (META(lending).id || ‘-BORROWERS’)
where lending.class = ‘com.entity.LendingApplication’
AND borrow. class = ‘com.entity.collateral.Borrowers’
AND ANY v IN borrow.borrowers SATISFIES v.members is not null END

Here I want the query to return data from LendingApplication only if the joined document Borrowers is missing .
I have tried using an OUTER JOIN

select ARRAY v FOR v IN borrow.borrowers WHEN v.members is not null END AS borrowers
FROM bkt_cpblending_dev lending
LEFT OUTER JOIN bkt_cpblending_ephemeral_dev borrow
ON KEYS (META(lending).id || ‘-BORROWERS’)
where lending._class = ‘com.entity.LendingApplication’
AND borrow._class = ‘com.entity.collateral.Borrowers’
AND ANY v IN borrow.borrowers SATISFIES v.members is not null END
AND META(lending).id = ‘1538163728398’

but it does not return any data.

5

You need to remove predicates on rightside other wise it will eliminate or You need to Use 5.5 with ANSI JOINS.

select ARRAY v FOR v IN borrow.borrowers WHEN v.members is not null END AS borrowers
FROM bkt_cpblending_dev lending
LEFT OUTER JOIN bkt_cpblending_ephemeral_dev borrow
ON KEYS (META(lending).id || ‘-BORROWERS’)
WHERE lending._class = ‘com.entity.LendingApplication’
AND META(lending).id = ‘1538163728398’;
6

Not sure I follow completely. I do not have any predicates on the right side. This is my complete query.

select lending.*, ARRAY v FOR v IN pledge.pledgers WHEN v.members is not null END AS pledgers
FROM bkt_cpblending_dev lending
LEFT OUTER JOIN bkt_cpblending_ephemeral_dev pledge
ON KEYS (META(lending).id || ‘-PLEDGERS’)
where lending._class = ‘com.entity.LendingApplication’
AND pledge._class = ‘com.entity.collateral.Pledgers’
AND META(lending).id = ‘1538163728398’

I don’t have an option is using version 5.5
Our CB version is
Enterprise Edition 5.0.1 build 500

7

When you use LEFT JOIN if key is MISSING it projects pledge as MISSING
Then in WHERE clause AND pledge._class = ‘com.entity.collateral.Pledgers’ evaluates false and removes from results.

You have two options:

  1. Remove AND pledge._class = ‘com.entity.collateral.Pledgers’
  2. Change it to AND (pledge IS MISSING OR pledge._class = ‘com.entity.collateral.Pledgers’)
8

Thank you that worked.