Comparisons and Sorts in OJAI Queries

When running OJAI queries with comparisons and sorts, you need to be aware of how different data types behave. You also need to understand how sorting works in Data Fabric DB queries. Depending on the component that runs the sort, you may encounter unexpected behavior.

OJAI supports comparisons using the QueryCondition interface. For information about how to use this interface, see Query Conditions in OJAI Applications.

When using the OJAI Query where and orderby, and comparing and sorting across different data types, there are subtleties you should take into consideration. See Using Comparable JSON Document Data Types in Comparisons and Sorts and Using Non-comparable JSON Document Data Types in Comparisons and Sorts for more information.

If you do not have a secondary index defined that can generate your query's specified orderby, then your query requires an explicit sort. If you have installed the OJAI Distributed Query Service, the service performs the sort. If you have not, the Data Fabric client performs the sort, but restricts the amount of data it can sort. The default sort limit is 5000 documents. For example, if your query returns 10,000 documents, and you specify a query result limit of 5000 documents, the Data Fabric client can perform the sort.

IMPORTANT
The Data Fabric client returns an error if your query result size exceeds the client's sort limit.

You can avoid errors due to the client sort limitation by adhering to the following guidelines:

  • If you know the largest possible query result size when your queries specify an order by, you can increase the sort limit of your client to that maximum size by setting the ojai.mapr.query.max-client-sort-limit parameter.

    The following code snippets increase the limit to 6000:

    query.setOption("ojai.mapr.query.max-client-sort-limit", 6000);
    NOTE
    This option is not applicable to the Java OJAI Thin Client.
    const query = {"$select":"col","$options":{"ojai":{"mapr":{"query":{"max-client-sort:6000}}}}
const stream = store.find(query)
    query = {"$select":"col","$options":{"ojai":{"mapr":{"query":{"max-client-sort:6000}}}}
query_result = store.find(query)
                var query = connection.NewQuery()
                .Select("col")
                .SetMaxClientSortLimit(6000)
                .Build();

            var queryResult = store.Find(query);
    query := map[string]interface{}{
    "$select":"col",
    "$options":map[string]interface{}{
        "ojai":map[string]interface{}{
            "mapr":map[string]interface{}{
                "query":map[string]interface{}{
                    "max-client-sort":6000}}}}}
queryResult, err :=  store.FindQueryMap(query, &client.FindOptions{})

    You can also set this option across all your OJAI clients by modifying a data access gateway property. See Administering the Data Access Gateway - Application Properties for details.

  • If you do not know the largest possible query result size, specify a limit in your queries. If your query result size exceeds that limit, the client sorts the entire result set but returns only a subset of the rows up to the specified limit. This avoids the error, but may result in unintended behavior if your application is not expecting a truncated result. You should take corrective action if necessary.

    See Querying with Order By for an example of how to set a query limit.