nosqlcouchbasesql++spring-data-couchbase

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


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.


Solution

  • 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;