I have the below kind of data stored in my collection called persons, I am using spring-data-couchbase
for reading this data in Java POJOs, and I am using the @Query
annotation in my PersonsRepository
class and writing SQL query for fetching data according to my need.
########## Document 1
{
"person": [
{
"id": "name",
"value": "abc"
},
{
"id": "age",
"value": 40
},
{
"id": "country",
"value": "usa"
}
],
"loggedOn": "2020-07-14"
}
########## Document 2
{
"person": [
{
"id": "name",
"value": "def"
},
{
"id": "age",
"value": 32
},
{
"id": "country",
"value": "uk"
}
],
"loggedOn": "2020-08-10"
}
Now I need to write SQL in Couchbase such that I can sort and paginate these documents by providing a value of person
's attribute.
I.e. sort by name or sort by age
Note: I do not want to download data in my Java POJO and then do sorting and paging, as it requires huge network bandwidth and I/O due to a large dataset.
To sort the values from ARRAY you need ARRAY position and which can vary in your case from document. You have following options:
Option 1) UNNEST the ARRAY, filter out and do sort based on ARRAY elements
SELECT p.id, p.`value`
FROM default AS d
UNNEST d.person AS p
WHERE p.id = "age" AND p.`value` > 30
ORDER BY p.`value`
OFFSET 10
LIMIT 5;
Pre CB 7.1
CREATE INDEX ix1 ON default (ALL ARRAY p.id FOR p IN person END);
CB 7.1+
CREATE INDEX ix1 ON default (ALL ARRAY FLATTEN_KEYS(p.id, p.`value`) FOR p IN person END);
Option 2) Find the ARRAY element you are interested and Sort based on that (USE primary index)
SELECT d.*
FROM default AS d
LET pObj = FIRST p FOR p IN person WHEN p.id = "age" END
WHERE pObj IS NOT NULL
ORDER BY pObj.`value`
OFFSET 10
LIMIT 5;
Option 3) Change the data model instead of ARRAY use as Object like below (if attributes are unique) and when required use OBJECT functions https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/objectfun.html
{
"name":"def",
"age": 32,
"country": "uk",
"loggedOn": "2020-08-10"
}
SELECT d.*
FROM default AS d
WHERE p.age > 30
ORDER BY p.age
OFFSET 10
LIMIT 5;