How to select the fields from the first document using join or subQuery?

SQL++
1

I have 2 sample documents like below ,
[
{
“facCd” : “AD”,
“orgs”:[
“5056”
],
“text”: " E/A"
},
{
“facCd” : “AF”,
“orgs”:[
“8061”
],
“text”: “L/A”
},
{
“facCd” : “AD”,
“orgs”:[
“8ABX”,
“DBX”,
“3BX”
],
“text”: " S/A"
},
{
“facCd” : “AD”,
“orgs”:[
“5051”,
“BR51”,
“21E”,
“45TX”
],
“text”: " L/S"
}
]
second one
[
{
“ogzNumber”: “8061”
},
{
“ogzNumber”: “8061”
},
{
“ogzNumber”: “7975”
},
{
“ogzNumber”: “BR51”
},
{
“ogzNumber”: “DXB”
},
{
“ogzNumber”: “8049”
},
{
“ogzNumber”: “8215”
},
{
“ogzNumber”: “5056”
}]

I want to retrieve info from 1st doc with the ids that are matching with second doc

1 Like
2

In 5.5 you can use ANSI JOINS https://blog.couchbase.com/ansi-join-support-n1ql/
Pre 5.5 you need relation ship through document key Checkout examples https://dzone.com/articles/visually-explaining-n1ql-joins

Assume second documents key is same as ogzNumber

SELECT f.*, s.* 
FROM default AS f JOIN default AS s ON KEYS f.orgs WHERE ....
3

Do I have to Unnest orgs in the first document ?
yes second document key is ogzNumber

4

If it is array of strings no need to UNNEST. Joins 1:n

5

I think I have posted in wrong way… Thank You for quick reply and patience.

I am using this Query which produces the below result
SELECT d.text,d.orgs,d.facCd FROM default d WHERE docType = ‘Dept’ AND d.orgs IS NOT MISSING AND ARRAY_COUNT(d.orgs)>0

[
{
“facCd” : “AD”,
“orgs”:[
“5056”
],
“text”: " E/A"
},
{
“facCd” : “AF”,
“orgs”:[
“8061”
],
“text”: “L/A”
},
{
“facCd” : “AD”,
“orgs”:[
“8ABX”,
“DBX”,
“3BX”
],
“text”: " S/A"
},
{
“facCd” : “AD”,
“orgs”:[
“5051”,
“BR51”,
“21E”,
“45TX”
],
“text”: " L/S"
}
]
I have the second Query which gives the second result
SELECT b.ogzNumber FROM default a UNNEST organizations b WHERE a.docType = ‘Org’
[
{
“ogzNumber”: “8061”
},
{
“ogzNumber”: “8061”
},
{
“ogzNumber”: “7975”
},
{
“ogzNumber”: “BR51”
},
{
“ogzNumber”: “DXB”
},
{
“ogzNumber”: “8049”
},
{
“ogzNumber”: “8215”
},
{
“ogzNumber”: “5056”
}]

Now I am looking for help to retrieve info from 1st doc with the ids that are matching with second doc.

6 
INSERT INTO default VALUES("d01", { "docType": "Dept", "facCd" : "AD", "orgs":[ "5056" ], "text": " E/A" }),
                          ("d02", { "docType": "Dept","facCd" : "AF", "orgs":[ "8061" ], "text": "L/A" }),
                          ("d03", { "docType": "Dept","facCd" : "AD", "orgs":[ "8ABX", "DBX", "3BX" ], "text": " S/A" }),
                          ("d04", { "docType": "Dept","facCd" : "AD", "orgs":[ "5051",    "BR51", "21E", "45TX" ], "text": " L/S" });
INSERT INTO default VALUES("o01",{ "docType":"Org", "organizations":[{"orgNumber":"8061"}, {"orgNumber":"5056"},{"orgNumber":"8ABX"}]});

SELECT d.text, d.orgs, d.facCd
FROM default d
LET orgs = ARRAY_FLATTEN((SELECT RAW b.organizations[*].orgNumber FROM default b WHERE b.docType = "Org"),2)
WHERE docType = "Dept" AND ANY v IN d.orgs SATISFIES v IN orgs END;

If still need help, Please post the Both documents and corresponding document keys.
How many documents you have docType = ‘Org’?
Do you want every organization you want list of the first documents?