Couchbase Lite - use coalesce / ifNullOrMissing in order by clause

Question

I have documents stored in a couchbase lite database. I use the query builder to request these documents in Java.

I would like to to order the retrieved documents given two properties: if one is missing, I'd like to use the value of another for the ordering.

For example, considering these data stored in the couchbase lite:

{
    "firstname":"Russell",
    "lastname":"Macdonald"
},
{
    "firstname":"Brielle"
    "birthname":"Vaughn"
    "lastname":"Bates"
},
{
    "firstname":"Molly"
    "birthname":"Arellano"
    "lastname":"Nichols"
}

I would like to order by birthname. But if the birthname is missing, the lastname should be used instead. The resulting order would be:

Molly Arellano (married Nichols)
Russell Macdonald
Brielle Vaughn (married Bates)

I tried passing the two successive properties to the order by clause. But, unsurprisingly, it did not work:

List<Result> results = select(all())
    .from(database(myDatabase))
    .orderBy(Ordering.property("birthname"), Ordering.property("lastname"))
    .execute().allResults()

score 1 · Answer 1 · answered Feb 15 '23 at 20:18

I don't think you can do this with the QueryBuilder interface. SQL+, however, has a function: "IFMISSINGORNULL(arg1, arg2)" whose value is its first argument, if ISMISSINGORNULL is false for that argument and its second argument otherwise. You should be able to use the query:

"select * from _ order by IFMISSINGORNULL(birthname, lastname)"

FWIW, the ResultSet produced by Query.execute() should be closed. It is AutoClosable so you might do something like this:

final Query query = db.createQuery("select * from _ order by IFMISSINGORNULL(birthname, lastname)");
try (ResultSet results = query.execute()) {
    // parse the results...
}

Couchbase Lite - use coalesce / ifNullOrMissing in order by clause

1 Answers1