Couchbase N1QL ORDER by true and field that doesn't exist

Question

I am trying to order by a field that is present sometimes and other times its not. Example data:

[
  { Name: Jeff, RegisteredUser: true },
  { Name: Jane },
  { Name: Jill, RegisteredUser: true },
]

My query is like so:

SELECT a.* 
        FROM table a 
        WHERE AND a.DocType = "User"
        ORDER BY Lower(a.RegisteredUser) ASC

However when I use the query above it doesn't order by properly at all (basically does nothing).

Answer 1

It does order. Please note LOWER on MISSING is MISSING and placed at first, LOWER on non-string value is NULL (because LOWER can be done only on string) which is placed after MISSING. In duplicated values can be placed any order with in duplicates, to avoid that provide more ORDER BY expressions. Checkout https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/orderby.html

You can remove LOWER because no lower is required on boolean field.

SELECT a.* 
FROM table a 
WHERE AND a.DocType = "User"
ORDER BY a.RegisteredUser ASC;