Issue In Subquery

SQL++
1

Hi @vsr1

I am facing one issue in below subquery

SELECT MAX([ instance.expiryTime, { ‘page’:p1[0].page.pageId, ‘instance’:instance.pageId , ‘_ID’: META(instance).id, ‘_CAS’:META(instance).cas }])[1].*
FROM bucket AS instance
LET p1 = (
SELECT *
FROM bucket page
WHERE page.type = ‘page’
AND page.pageId IN [“testing-xdcr”,“xdcr-23”])
WHERE instance.isStacked = FALSE
AND instance.pageId IN [ p1[0].page.pageId ]
AND instance.type = ‘pagedef’
AND ((instance.effectiveTime <= 1617261825616
AND 1617261825616 <= instance.expiryTime)
OR (instance.effectiveTime <= 1617261825616
AND (instance.expiryTime IS NULL
OR instance.expiryTime IS MISSING)))
GROUP BY p1[0].page.pageId

This is working fine and returning with correct data, but as you see the p1 will be an array of page objects, it is only picking the zeroth element, as I am adding p1[0]. I want it to consider all objects of the page and further map it each with instance.pageId .

The response i am getting -

[
{
“_CAS”: 1617260847315222528,
“_ID”: “49107e1c-024b-4f08-a6f4-4e8fde3d8902”,
“instance”: “testing-xdcr”,
“page”: “testing-xdcr”
}
]

But I want response for both of my page Ids, something like this,

[
{
“_CAS”: 1617260847315222528,
“_ID”: “49107e1c-024b-4f08-a6f4-4e8fde3d8902”,
“instance”: “testing-xdcr”,
“page”: “testing-xdcr”
} ,
{
“_CAS”: 1617260847315222528,
“_ID”: “49107e1c-024b-4f08-a6f4-4e8fde3d8902”,
“instance”: “xdc-23”,
“page”: “xdcr-23”
}
]

2 
WITH pages AS ( SELECT page.pageId FROM bucket AS page
                WHERE page.type = "page"
                AND page.pageId IN ["testing-xdcr","xdcr-23"])
SELECT MAX([ instance.expiryTime, { instance.pageId , "_ID": META(instance).id, "_CAS":META(instance).cas }])[1].*
FROM bucket AS instance JOIN pages AS p ON instance.pageId = p.pageId
WHERE instance.isStacked = FALSE
       AND instance.type = "pagedef"
      AND (   (instance.effectiveTime <= 1617261825616 AND 1617261825616 <= instance.expiryTime)
            OR (instance.effectiveTime <= 1617261825616 AND (instance.expiryTime IS NULL
            OR instance.expiryTime IS MISSING)))
GROUP BY instance.pageId;
3

Hi @vsr1 ,

is it possible to work with subquery rather than join ?
Join increase the lookup time while the subquery , we are around 20ms.

4

It is still subquery (materialized). The results will be ARRAY. If you need to every instance and look every element of pages. You need to use JOIN.

If using EE you can use HASH JOIN.

As you are looking only pageId form pages and no other info

WITH pages AS ( SELECT RAW page.pageId FROM bucket AS page
                WHERE page.type = "page"
                AND page.pageId IN ["testing-xdcr","xdcr-23"])
SELECT MAX([ instance.expiryTime, { instance.pageId , "_ID": META(instance).id, "_CAS":META(instance).cas }])[1].*
FROM bucket AS instance
WHERE instance.isStacked = FALSE
       AND instance.type = "pagedef"
       AND instance.pageId  IN pages
       AND (   (instance.effectiveTime <= 1617261825616 AND 1617261825616 <= instance.expiryTime)
            OR (instance.effectiveTime <= 1617261825616 AND (instance.expiryTime IS NULL
            OR instance.expiryTime IS MISSING)))
GROUP BY instance.pageId;


CREATE INDEX ix1 ON bucket(pageId) WHERE type = "page";
CREATE INDEX ix2 ON bucket(isStacked, pageId, effectiveTime,expiryTime, META().cas ) WHERE type = "pagedef";

If required adjust indexes.