How to sort documents by value of json object in couchbase query?

Question

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.

Answer 1

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;