Elasticsearch sort by custom created_at field

Question

I've got a created_at field in my Elastic Search database and I'm trying to pull out data and sort it by that field. The field was stored with a mapping property with the date format, with the fielddata key set to true, but I still get the error:

Text fields are not optimised for operations that require per-document field data like aggregations and sorting, so these operations are disabled by default. Please use a keyword field instead. Alternatively, set fielddata=true on [created_at] in order to load field data by uninverting the inverted index. Note that this can use significant memory.

One suggestion is that I can add the word keyword to my field to search it, but this seems to tell me that:

created_at is not defined

I'm using Javascript, and I know you can't just add the (dot) character, so I've wrapped it and it still isn't working. elastic.find is just a function I've written to pull data, if I remove the sort array, it works.

const results = await elastic.find('my table', {
  query: {
    range: {
      created_at: {
        gte: moment(from).format('YYYY-MM-DD HH:MM:SS')
      }
    }
  },
  sort: [{
    [created_at.keyword]: 'asc' // seems to be undefined
  }]
})

Why can't I access created_at.keyword?

Answer 1

Your dates shouldn't be strings/kewords but rather mapped as dates. Let me walk you through it.

1. Set up the package & client

const { Client } = require("@elastic/elasticsearch");
const client = new Client({
  node: "http://localhost:9200"
});

const INDEX_NAME = "my_table";

2. Create the index

(async () => {
  const { body, statusCode } = await client.indices.create(
    {
      index: INDEX_NAME,
      body: {
        mappings: {
          properties: {
            created_at: {
              type: "date",
              format: "yyyy-MM-dd HH:mm:ss"
            }
          }
        }
      }
    },
    { ignore: [400] }
  );

  if (body.error) {
    console.warn("createResponse err", body.error);
  } else {
    console.info("createResponse", { body, statusCode });
  }
})();

3. Add some document(s)

(async () => {
  const { body, statusCode } = await client.bulk({
    body: [
      // Doc #1
      { index: { _index: INDEX_NAME, _id: 1 } },
      { created_at: "2021-02-19 00:00:00" },

      // Doc #2
      { index: { _index: INDEX_NAME, _id: 2 } },
      { created_at: "2021-02-19 00:02:00" }
    ]
  });

  if (body.error) {
    console.warn("bulkResponse err", body.error);
  } else {
    console.info("bulkResponse", { body, statusCode });
  }
})();

4. Search & sort

(async () => {
  const { body, statusCode } = await client.search({
    index: INDEX_NAME,
    body: {
      size: 10,
      query: {
        range: {
          created_at: {
            gte: "2021-02-18 00:00:00"
          }
        }
      },
      sort: [
        {
          created_at: "asc"
        }
      ]
    }
  });

  if (body.error) {
    console.warn("searchResponse err", body.error);
  } else {
    // pretty print
    console.dir({ searchResponse: { body, statusCode } }, { depth: null });
  }
})();

Here are some more official examples.

BTW the above snippets are meant to work independently of each other but in a real app you wouldn't want to use separate async closures but rather one async process with multiple awaits. What I'm saying is, the execution order matters.