i have my records in the below structure in my couchbase database, i want to look for “name” = “about”, with two previous records and two forward records:
WITH snames AS ( WITH names AS ([ { "name": "abandon", "city": "-" }, { "name": "ability", "city": "-" }, { "name": "able", "city": "-" }, { "name": "about", "city": "-" }, { "name": "above", "city": "-" }, { "name": "abroad", "city": "-" }, { "name": "abu se", "city": "-" } ]) SELECT n.* FROM names n ORDER BY n.name),
noffset AS (2),
foundpos AS (FIRST pos FOR pos:v IN snames WHEN v.name == "about" END),
startpos AS (GREATEST(0, foundpos - noffset)),
endpos AS (LEAST(ARRAY_LENGTH(snames), IFMISSING(FIRST pos FOR pos:v IN snames WHEN pos >= foundpos AND v.name != "about" END + noffset,0)))
SELECT n.* FROM snames[startpos:endpos] AS n;
This handles what you looking is present/duplicates, no preceding/no following entries
Thanks for taking your time in replying to my problem, but this solution cannot solve my problem, let me explain again with another example to clarify well:
suppose i have my couchbase database records as following :
from this i would like to query 5 records where my search term record is in the middle, if i am searching for “name” = “option6”, then i am expecting my query to respond me the below dataset:
replace hard coded WITH clause your bucket query
change noffset, v.name == “about”
It will work
WITH snames AS ( SELECT n.* FROM bucket1 AS n WHERE n.name IS NOT NULL ORDER BY n.name),
noffset AS (3),
foundpos AS (FIRST pos FOR pos:v IN snames WHEN v.name == "option 6" END),
startpos AS (GREATEST(0, foundpos - noffset)),
endpos AS (LEAST(ARRAY_LENGTH(snames), IFMISSING(FIRST pos FOR pos:v IN snames WHEN pos >= foundpos AND v.name != "about" END + noffset,0)))
SELECT n.* FROM snames[startpos:endpos] AS n;
@vsr1 thanks for your kind replay again. but after i looked around i noticed that the “WITH” clause is not supported by couchbase "Community Edition 6.0.0 ", the one i am using. i also checked in the couchbase documentation they have used it for 6.6. but unfortunately i can’t use it in my version of couchbase.
SELECT n.*
FROM ( SELECT snames[startpos:endpos] AS snames
FROM (SELECT (FIRST pos FOR pos:v IN snames WHEN v.name == "option 6" END) AS foundpos,
3 AS noffset,
snames
LET snames = (SELECT n.* FROM bucket1 AS n WHERE n.name IS NOT NULL ORDER BY n.name)) AS d
LET startpos = GREATEST(0, d.foundpos - d.noffset),
endpos = LEAST(ARRAY_LENGTH(d.snames), IFMISSING(FIRST pos FOR pos:v IN d.snames WHEN pos >= d.foundpos AND v.name != "about" END + d.noffset,0)) ) AS d2 UNNEST d2.snames AS n;
there is one more condition that i want to ask, if my search term is the last element in that case the response of the query is empty, for example, instead of searching for “name” = “option 6” i want to search for “name” = “option 8” which is the last element in the database query. in that case if i need my query to return 5 records with my search term in the middle i would expect at least like this:
[
{
“name”: “option 4”
},
{
“name”: “option 5”
},
{
“name”: “option 6”
},
{
“name”: “option 7”
},
{
“name”: “option 8”
}
]
SELECT n.*
FROM ( SELECT snames[startpos:endpos] AS snames
FROM (SELECT (FIRST pos FOR pos:v IN snames WHEN v.name == "option 6" END) AS foundpos,
2 AS noffset,
snames
LET snames = (SELECT n.* FROM bucket1 AS n WHERE n.name IS NOT NULL ORDER BY n.name)) AS d
LET startpos = GREATEST(0, d.foundpos - (d.noffset + GREATEST (0, d.noffset - (ARRAY_LENGTH(d.snames) -1 - d.foundpos)))),
endpos = LEAST(ARRAY_LENGTH(d.snames), IFMISSING(FIRST pos FOR pos:v IN d.snames WHEN pos > d.foundpos END + d.noffset,IFMISSING(d.foundpos+1,0)))
) AS d2 UNNEST d2.snames AS n;
SELECT d.docs
FROM (SELECT b.name, ARRAY_AGG(b) OVER (ORDER BY b.name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS docs
FROM bucket1 AS b WHERE b.name IS NOT NULL) AS d
WHERE d.name = "option 6";
Always give 5 entires if found entry (2 before/2 after, on ends next or prev one), none if not found
SELECT d1.docs[startpos:endpos] AS docs
FROM (SELECT d.docs, d.noffset, d.name
FROM ( SELECT b.name, ARRAY_AGG(b) OVER (ORDER BY b.name ROWS BETWEEN 2*2 PRECEDING AND 2*2 FOLLOWING) AS docs, 2 AS noffset
FROM bucket1 AS b WHERE b.name IS NOT NULL) AS d
WHERE d.name = "option 6") AS d1
LET foundpos = (FIRST pos FOR pos:v IN d1.docs WHEN v.name == d1.name END),
startpos = GREATEST(0, foundpos - (d1.noffset + GREATEST (0, d1.noffset - (ARRAY_LENGTH(d1.docs) -1 - foundpos)))),
endpos = LEAST(ARRAY_LENGTH(d1.docs), startpos+2*d1.noffset+1);
pre 6.5
SELECT n.*
FROM ( SELECT snames[startpos:endpos] AS snames
FROM (SELECT (FIRST pos FOR pos:v IN snames WHEN v.name == "option 6" END) AS foundpos,
2 AS noffset,
snames
LET snames = (SELECT n.* FROM bucket1 AS n WHERE n.name IS NOT NULL ORDER BY n.name)) AS d
LET startpos = GREATEST(0, d.foundpos - (d.noffset + GREATEST (0, d.noffset - (ARRAY_LENGTH(d.snames) -1 - d.foundpos)))),
endpos = LEAST(ARRAY_LENGTH(d.snames), startpos+2*d.noffset+1)
) AS d2 UNNEST d2.snames AS n;
OR
WITH searchdoclist AS ( (WITH docs AS (SELECT b.name, ARRAY_AGG(b) OVER (ORDER BY b.name ROWS BETWEEN 2*2 PRECEDING AND 2 FOLLOWING) AS docs
FROM bucket1 AS b WHERE b.name IS NOT NULL)
SELECT (SELECT RAW sd FROM d.docs AS sd ORDER BY sd.name DESC LIMIT 5) AS docs
FROM docs AS d
WHERE d.name = "option 8" LIMIT 1)[0])
SELECT n.*
FROM searchdoclist.docs AS n
ORDER BY n.name;
,