Elasticsearch Node.js date operators in query search

Question

I'm storing data in my local Node JS Elastic Search database, now I've inserted records and one of the fields is a created_at field, which I've stored with new Date(), however, upon retrieval of the data, it seems that these are Strings, and thus my query doesn't seem to return any results.

My data looks like:

{
    "_index": "my_table",
    "_type": "_doc",
    "_id": "V1mouXcBt3ZsPNCwd3A1",
    "_version": 1,
    "_seq_no": 0,
    "_primary_term": 1,
    "found": true,
    "_source": {
        "properties": {
            "created_at": {
                "type": "date",
                "format": "yyyy-MM-dd HH:mm:ss"
            },
            "updated_at": {
                "type": "date",
                "format": "yyyy-MM-dd HH:mm:ss"
            }
        },
        "data": {
            "name": "performance_overview",
            "friendly_name": "Performance Overview",
            "data": {
                "cached": "9:39:21am",
                "from": "02/19/2021 00:00:00",
                "to": "02/19/2021 23:59:59",
                "success": true,
                "message": "message",
                "sessions": "265",
                "leads": "123",
                "conversion_rate": "46.42"
            },
            "created_at": "2021-02-19 09:02:16",
            "updated_at": "2021-02-19 09:02:16"
        }
    }
}

I'm using Moment JS, and have formatted the dates, and attempted what I believe is accurate for field mappings, although I'm new to Elastic Search and this seems to not return any results either.

const from = moment(from).startOf('day')

const results = await elastic.find('my_table', {
  query: {
    range: {
      created_at: {
        gte: moment(from).format('YYYY-MM-DD HH:MM:SS'),
      }
    }
  }
})

elastic.find is a custom function that I've written which looks like the following and is exported from another JS file:

const find = async (table, data) => {
  try {
    const found = await client.search({
      index: table,
      body: data
    }, {
      ignore: [404],
      maxRetries: 3
    })

    return found.body
  } catch (err) {

    return err
  }
}

Jozef - Spatialized.io · Accepted Answer · 2021-02-18 20:07:11Z

2

Elasticsearch is a JSON-in, JSON-out interface that stores your datetime data as strings of a given format (could be numeric (milli)seconds since the epoch too, if you choose so.)

As to why new Date() was converted to a string — at some point before the network transport, the JS client will serialize your documents-containing request body by calling JSON.stringify on it. When this function is called on a JS object, the JS runtime looks for an implementation of the .toJSON method on that object and serialize the value returned by the this method. You can verify this by running:

const date = new Date();
console.assert(`"${date.toJSON()}"` === JSON.stringify(date))

Now, in your elastic.find('my_table', {...}) call you're attempting two things at once — setting the mapping and querying the index. That's not going to work.

You've got to define your mapping before you ingest any documents. When you do that, make sure your date fields have a proper format to prevent inconsistencies down the line:

{
  "properties": {
    "created_at": {
      "type": "date",
      "format": "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"
    }
  }
}

After that add some documents to your table/index (through the index operation).

Then and only then will you be able to use your range query. Here's a good working example.

edited Feb 18, 2021 at 20:07

answered Feb 18, 2021 at 18:10

Jozef - Spatialized.io

17k4 gold badges29 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ryan H Over a year ago

This is a useful answer and has sort of helped me in getting data into my table with the properties object, however, upon trying to retrieve data, still, nothing is coming through. You can see that the created_at field is pretty much right now, and my query is getting the date/time from the start of the day, so there should be results returned, but there isn't. What am I missing?

Jozef - Spatialized.io Over a year ago

It's still not it. The mapping properties should not be in the data itself but rather in the index mapping (table schema). What you did is that you put your table schema inside of the table row. Re-read my answer, follow the links (esp. the "define your mapping" part) and see if you get it up and running.

Jozef - Spatialized.io Over a year ago

Did you figure it out @RyanHolton?

Collectives™ on Stack Overflow

Elasticsearch Node.js date operators in query search

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related