Subselect result on Join not working

SQL++
,
1

Hi I have two different type of documents as like following example and i am not able to find the result when applying subselect result with join.

SELECT pageDetails FROM
( SELECT *,META().id FROM DB WHERE type = ‘page’
and ANY pageDetails IN details SATISFIES pageDetails.number LIKE “%140709%” ) as a
JOIN DB pageDetails ON KEYS a.id
WHERE pageDetails.type = ‘pageDetail’

Document 1:

{
     id": "1",
     "type" : "page"
      "details": [
        {
          "number": "709"
        },
        {
          "number": "809"
        }
      ]
}

Document2:

{
      "id" : 1,
      "type": "pageDetail",
}
2

use ANSI join ANSI JOIN Support in N1QL | The Couchbase Blog

SELECT pd.*
FROM DB AS p JOIN DB AS pd ON p.id = TO_STRING(pd.id) AND pd.type = "pageDetail"
WHERE p.type = "page" AND ANY v IN p.details SATISFIES v.`number` LIKE "%140709%";

CREATE INDEX ix1 ON DB(TO_STRING(id)) WHERE type = "pageDetail";
3

Hi @vsr1,

I am having similar type of requirement. Three type of documents are like below.
Document 1
{ type:"book", number:"123" }
Document 2
{ type:"page", id:"1", "details": [ { "number": "123" }, { "number": "809" } ] }
Document 3
{ type:"pageDetail", id:"1" }

Here if I search with “123” I want to return “book” type docs where “number” like “123” as well as “pageDetail” type documents where “page.details.number” like 123 and page.id = pageDetail.id
Desired output
[{ type:"book", number:"123" pageDetail:{ "id" : 1, "type": "pageDetail", } },{ type:"book", number:"1234" pageDetail:{ "id" : 2, "type": "pageDetail", } }]

I tried in many ways using JOINS but couldn’t able get the result.
Please help me to get desired output.

Thanks

4 
SELECT b1.*, pd.*
FROM DB AS p JOIN DB AS pd ON p.id = TO_STRING(pd.id) AND pd.type = "pageDetail"
UNNEST (SELECT b.* FROM DB AS b WHERE b.type = "book" AND b.`number` LIKE "%123%") AS b1
WHERE p.type = "page" AND ANY v IN p.details SATISFIES v.`number` LIKE "%140709%";
5

Thanks @vsr1 for quick reply. If I execute the above query then the result I am getting array of JSON objects. Each object having keys of both “book” and “pageDetail” docs keys. But those two docs have some common keys, those are overridden by “pageDetail” doc keys. I need “pageDetail” doc as separate value for key “pageDetail” in “book” object. And How to get META.id() for book doc in same query?

6 
SELECT b1.*, pd AS PageDetails
FROM DB AS p JOIN DB AS pd ON p.id = TO_STRING(pd.id) AND pd.type = "pageDetail"
UNNEST (SELECT b.*, META(b).id FROM DB AS b WHERE b.type = "book" AND b.`number` LIKE "%123%") AS b1
WHERE p.type = "page" AND ANY v IN p.details SATISFIES v.`number` LIKE "%140709%";
7

Thanks @vsr1 for quick reply

8

If I execute the above query I am getting only “book” docs those are having relation with “page” and “pageDetail” docs. If I want to fetch all “book” docs “number” Like 123 along with “pageDetail” docs those having relationship with “book” docs(All “book” docs number like 123 and also “pageDetail” docs those having relation with book docs). In this case how to change the query?

9

You should join via relation ship field. Follow the link above. If need help Provide the sample documents with relation of the fields.

10

Hi @vsr1 , Please check below documents

Document 1
{ type:"book", number:"123" },{ type:"book", number:"1234" }
Document 2
{ type:"page", id:"1", "details": [ { "number": "123" }, { "number": "809" } ] }
Document 3
{ type:"pageDetail", id:"1" }

Relation between “book” type doc and “page” type doc is “number”. Relation between “page” and “pageDetail” is “id”.

Here if I search with “123” I want to return “book” type docs where “number” like “123” as well as “pageDetail” type documents where “page.details.number” = “book.number” and page.id = pageDetail.id(Here I want to fetch all “book” docs "number " like 123 and “pageDetail” docs those are having relation with filtered “book” docs )

Desired output
[{ type:"book", number:"123" pageDetail:{ "id" : 1, "type": "pageDetail", } },{ type:"book", number:"1234" }]

11 
SELECT b.*,  pd AS pageDetail
FROM DB AS b
JOIN DB AS p ON p.type = "page" AND ANY v IN p.details SATISFIES v.`number` = b.`number` END
JOIN DB AS pd ON p.id = pd.id AND pd.type = "pageDetail"
WHERE b.type = "book" AND b.`number` LIKE "%123%";

CREATE INDEX ix1 ON DB(id) WHERE type = "pageDetail";
CREATE INDEX ix2 ON DB(DISTINCT ARRAY v.`number` FOR v IN details END, id) WHERE type = "page";