How to Sort a Query by a Numeric Field in Elasticsearch 6 using NodeJS

Introduction

When you search for data in Elasticsearch, you often want your results to come back in a certain order. Perhaps you want to see student grades sorted from the highest to the lowest, or you’d like to have your inventory listed from the least number of units to the highest for re-ordering purposes. Fortunately, Elasticsearch makes it easy to sort your data in either ascending or descending order. In this tutorial, we’ll show you how to sort a query by a numeric field in Elasticsearch 6 using NodeJS. If you’re already familiar with searching, sorting, Javascript, and Elasticsearch, you can skip ahead to Just the Code.

Prerequisites

Before we show you how to perform fuzzy query matches with Elasticsearch in Javascript, it’s important to make sure a few prerequisites are in place. There are only a few of system requirements for this task: NodeJS needs to be installed The elasticsearch npm module installed. A simple npm install elasticsearch should work in most cases. Elasticsearch also needs to be installed and running. * In our example, we have Elasticsearch installed locally using the default port of 9200. If your Elasticsearch installation is running on a different server, you’ll need to modify your javascript syntax accordingly.

Sort ascending using the sort parameter

Let’s look at an example that uses an index called store, which represents a small grocery store. This store index contains a type called products which lists the store’s products. To keep things simple, our example dataset will only contain a handful of products with just the following fields: id, price, quantity, and department. The code below shows the JSON used to create the dataset:

idnamepricequantitydepartment
1Multi-Grain Cereal4.994Packaged Foods
21lb Ground Beef3.9929Meat and Seafood
3Dozen Apples2.4912Produce
4Chocolate Bar1.292Packaged Foods, Checkout
51 Gallon Milk3.2916Dairy
60.5lb Jumbo Shrimp5.2912Meat and Seafood
7Wheat Bread1.295Bakery
8Pepperoni Pizza2.995Frozen
912 Pack Cola5.296Packaged Foods
10Lime Juice0.9920Produce
1112 Pack Cherry Cola5.595Packaged Foods
121 Gallon Soy Milk3.3910Dairy
131 Gallon Vanilla Soy Milk3.499Dairy
141 Gallon Orange Juice3.294Juice

Here is the json we used to define the mapping if our index:

{
    "mappings": {
        "products": {
            "properties" : {
                "name": { "type": "text"},
                "price": { "type": "double"},
                "quantity": { "type": "integer"},
                "department": { "type": "keyword"}
            }
        }
    }
}

For our example, let’s imagine we want to determine which items our store is running low on. We’d accomplish this by querying the database and having our results sorted in ascending order by quantity. Since our dataset is small, we can glance at our data and see that the “Chocolate Bar” would appear first because it’s the lowest in quantity. The following Javascript the query set to match_all, which will return all documents. Note that the query is set to sort on the quantity field in ascending order with "quantity":"asc".

File app.js:

var elasticsearch = require("elasticsearch");

var client = new elasticsearch.Client({
  hosts: ["http://localhost:9200"]
});

/* Sort Query by Numeric Field Ascending */
client.search({
  size: 1,
  index: 'store',
  type: 'products',
  body: {
        query: {
            "match_all": {}
        },
        sort: [{"quantity": "asc"}]
    }
}).then(function(resp) {
  console.log("Successful query!");
  console.log(JSON.stringify(resp, null, 4));
}, function(err) {
  console.trace(err.message);
});

Run app.js in the terminal like this:

$ node app.js

Response:

Successful query!
{
    "took": 1,
    "timed_out": false,
    "_shards": {
        "total": 5,
        "successful": 5,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": 13,
        "max_score": null,
        "hits": [
            {
                "_index": "store",
                "_type": "products",
                "_id": "4",
                "_score": null,
                "_source": {
                    "id": "4",
                    "name": "Chocolate Bar",
                    "price": 1.29,
                    "quantity": 2,
                    "department": [
                        "Packaged Foods",
                        "Checkout"
                    ]
                },
                "sort": [
                    2
                ]
            }
        ]
    }
}

We used the size parameter to limit the output to only the first result, and as you can “Chocolate Bar” was indeed the first result because it has the smallest quantity.

Sort descending using the sort parameter

Now that we’ve seen how to sort in ascending order, let’s look at the same query in reverse order simply by changing the sort to "quantity": "desc". For this query, the first result shown should be “1lb Ground Beef” because it has the highest quantity. Let’s run this query and see if it works:

File app.js:

var elasticsearch = require("elasticsearch");

var client = new elasticsearch.Client({
  hosts: ["http://localhost:9200"]
});

/* Sort Query by Numeric Field Descending */
client.search({
  size: 1,
  index: 'store',
  type: 'products',
  body: {
        query: {
            "match_all": {}
        },
        sort: [{"quantity": "desc"}]
    }
}).then(function(resp) {
  console.log("Successful query!");
  console.log(JSON.stringify(resp, null, 4));
}, function(err) {
  console.trace(err.message);
});

Run app.js in the terminal like this:

$ node app.js

Response:

Successful query!
{
    "took": 1,
    "timed_out": false,
    "_shards": {
        "total": 5,
        "successful": 5,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": 13,
        "max_score": null,
        "hits": [
            {
                "_index": "store",
                "_type": "products",
                "_id": "2",
                "_score": null,
                "_source": {
                    "id": "2",
                    "name": "1lb Ground Beef",
                    "price": 3.99,
                    "quantity": 29,
                    "department": [
                        "Meat and Seafood"
                    ]
                },
                "sort": [
                    29
                ]
            }
        ]
    }
}

We can confirm that this query sorts the results correctly since the first item returned is indeed the “1lb Ground Beef”.

Conclusion

When you’re searching for data, you often want your results to be returned in a particular order. Elasticsearch makes this task simple with its sorting functionality, allowing you to sort a field in either ascending or descending order. With the step-by-step instructions included in this tutorial, you’ll have no trouble sorting figuring out the Javascript code needed to query by a numeric field in Elasticsearch 6.

Just the Code

If you’re already familiar with basic searching and sorting principles, here’s all the code you need to sort a query by a numeric field in Elasticsearch 6:

Sort Ascending:

var elasticsearch = require("elasticsearch");

var client = new elasticsearch.Client({
  hosts: ["http://localhost:9200"]
});

/* Sort Query by Numeric Field Descending */
client.search({
  size: 1,
  index: 'store',
  type: 'products',
  body: {
        query: {
            "match_all": {}
        },
        sort: [{"quantity": "asc"}]
    }
}).then(function(resp) {
  console.log("Successful query!");
  console.log(JSON.stringify(resp, null, 4));
}, function(err) {
  console.trace(err.message);
});

Sort Descending:

var elasticsearch = require("elasticsearch");

var client = new elasticsearch.Client({
  hosts: ["http://localhost:9200"]
});

/* Sort Query by Numeric Field Descending */
client.search({
  size: 1,
  index: 'store',
  type: 'products',
  body: {
        query: {
            "match_all": {}
        },
        sort: [{"quantity": "desc"}]
    }
}).then(function(resp) {
  console.log("Successful query!");
  console.log(JSON.stringify(resp, null, 4));
}, function(err) {
  console.trace(err.message);
});

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.