Hello, i want to query five records from my couchbase database in a way that my search term is the one in the middle, can any one help me?

Couchbase Server
#1

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:

[
  {
    "name": "abandon",
    "city": "-"
  },
  {
    "name": "ability",
    "city": "-"
  },
  {
    "name": "able",
    "city": "-"
  },
  {
    "name": "about",
    "city": "-"
  },
  {
    "name": "above",
    "city": "-"
  },
  {
    "name": "abroad",
    "city": "-"
  },
  {
    "name": "abu    se",
        "city": "-"
      }
    ]

i am waiting for the query to give the response as below:

[
  {
    "name": "ability",
    "city": "-"
  },
  {
    "name": "able",
    "city": "-"
  },
  {
    "name": "about",
    "city": "-"
  },
  {
    "name": "above",
    "city": "-"
  },
  {
    "name": "abroad",
    "city": "-"
  }
]
#2 
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

#3

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 :

[
{
“name”: “option 1”
},
{
“name”: “option 2”
},
{
“name”: “option 3”
},
{
“name”: “option 4”
},
{
“name”: “option 5”
},
{
“name”: “option 6”
},
{
“name”: “option 7”
},
{
“name”: “option 8”
}
]

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:

[
{
“name”: “option 4”
},
{
“name”: “option 5”
},
{
“name”: “option 6”
},
{
“name”: “option 7”
},
{
“name”: “option 8”
}
]

#4

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;
#5

@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.

#6 
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;
1 Like
#7

@vsr1 yes it worked, thank you so much :slight_smile: ,

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”
}
]

#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;
1 Like
#9

In 6.5 EE Use OLAP window frames

Getting n documents before n after easy

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;
1 Like
#10

@vsr1 it worked, thank you so much :slight_smile: