Why ORDER BY in Java N1QL not working as intended

Question

When I run SELECT * FROM projects ORDER BY createdAt desc this query in Couchbase Web UI result ordered by as it should be but not in java.

Java Code

@Override
    public List<Project> getAllProjects(String requestedOrder, Integer page, Integer pageOffset) {
        String statement = "SELECT projects.* FROM projects ORDER BY $requestedOrder DESC OFFSET $offset LIMIT $pageOffset";
        QueryOptions queryOptions = queryOptions().parameters(
                JsonObject.create().put("offset", (page - 1) * pageOffset)
                        .put("pageOffset", pageOffset)
                        .put("requestedOrder", requestedOrder));
        QueryResult queryResult = couchbaseCluster.query(statement, queryOptions);
        return queryResult.rowsAs(Project.class);
    }

Web UI result

[
  {
    "projects": {
      "createdAt": 1603804921950,
      "name": "Third Project",
      ...
    }
  },
  {
    "projects": {
      "createdAt": 1603804915827,
      "name": "Second Project",
      ...
    }
  },
  {
    "projects": {
      "createdAt": 1603804909410,
      "name": "First Project"
      ...
    }
  }
]

Java SDK Result

[
  {
    "name": "Second Project",
    "createdAt": "2020-10-27T13:21:55.827+00:00",
    ...
  },
  {
    "name": "Third Project",
    "createdAt": "2020-10-27T13:22:01.950+00:00",
    ...
  },
  {
    "name": "First Project",
    "createdAt": "2020-10-27T13:21:49.410+00:00",
    ...
  }
]

When i change my statement to this it orders correctly but why it's not ordering correctly using put method.

String statement = "SELECT projects.* FROM projects ORDER BY " + requestedOrder + " DESC OFFSET $offset LIMIT $pageOffset";

score 6 · Accepted Answer · edited Oct 28 '20 at 08:06

String statement = "SELECT projects.* FROM projects ORDER BY $requestedOrder DESC OFFSET $offset LIMIT $pageOffset";

In above query you are passing The ORDER BY expressions as named parameter, which is value. The value will be constant and same for all the documents in query. Sort on same value is NOOP i.e. it may not sort anything.

ORDER BY expressions must depend on the field of the documents. Each time if you need different field you must use different queries.

NOTE: JSON is schema less and no physical position of field. ORDER BY number will treat as constant vs some RDBS database uses projection field position as sort.

If $requestedOrder is not a nested field try replace the query string with the following. $requestedOrder must evaluate to string and must be the field of the document (not nested). Example $requestedOrder = "createdAt" The During execution of sort evaluates p.createdAt (i.e same as ORDER BY p.createdAt)

 String statement = "SELECT p.* FROM projects AS p ORDER BY p.[$requestedOrder] DESC OFFSET $offset LIMIT $pageOffset";

Your answer is correct `ORDER BY 'createdAt'`. Though most drivers will assume a string, it would be interesting to check an int requestedOrder: `ORDER BY 2` and such. — Joop Eggen, Oct 27 '20 at 14:26

Why ORDER BY in Java N1QL not working as intended

1 Answers1