ES|QL query message format

[pruna]

Hi! I need to query elements for a specific month in a date field.

For that, I'm trying my first ES|QL query, as follows:

{
    "query": """
FROM facts
| WHERE DATE_PART('month', date_start) = '06'
    """
}

by doing a POST to /_query?format=txt , using Postman.

If I define the raw body as Text, I get this error:

{
    "error": "Content-Type header [text/plain] is not supported",
    "status": 406
}

And if I try JSON, I get a different error about incorrect format (see below). How should I configure it to be accepted by ES?

{
    "error": {
        "root_cause": [
            {
                "type": "x_content_parse_exception",
                "reason": "[2:16] Unexpected character ('\"' (code 34)): was expecting comma to separate Object entries\n at [Source: (byte[])\"{\r\n    \"query\": \"\"\"\r\nFROM facts\r\n| WHERE DATE_PART('month', date_start) = '06'\r\n    \"\"\"\r\n}\"; line: 2, column: 16]"
            }
        ],
        "type": "x_content_parse_exception",
        "reason": "[2:16] Unexpected character ('\"' (code 34)): was expecting comma to separate Object entries\n at [Source: (byte[])\"{\r\n    \"query\": \"\"\"\r\nFROM facts\r\n| WHERE DATE_PART('month', date_start) = '06'\r\n    \"\"\"\r\n}\"; line: 2, column: 16]",
        "caused_by": {
            "type": "json_parse_exception",
            "reason": "Unexpected character ('\"' (code 34)): was expecting comma to separate Object entries\n at [Source: (byte[])\"{\r\n    \"query\": \"\"\"\r\nFROM facts\r\n| WHERE DATE_PART('month', date_start) = '06'\r\n    \"\"\"\r\n}\"; line: 2, column: 16]"
        }
    },
    "status": 400
}

[stephenb (Stephen Brown)]

Hi @pruna Welcome to the community...
Use raw with type json

The triple quotes don't work that is a kibana convention so yours will be

{
    "query": "FROM facts | WHERE DATE_PART('month', date_start) = '06'"
}

[pruna]

Hi @stephenb , thank for the welcome, and the fast response! Yes, I was using raw. The change from triple quotes to just double quotes and one line did the trick.

Now I can run ES|QL queries, but I'm struggling to get my month comparison working.

Here's a list of several alternatives I tried, all reporting different errors:

FROM facts | WHERE match(DATE_FORMAT(date_start, \"MM\"), \"06\")
FROM facts | WHERE DATE_EXTRACT(\"month\", date_start) : 6
FROM facts | WHERE match(DATE_EXTRACT(\"month\", date_start),6)
FROM facts | WHERE match(DATEPART(\"month\", date_start),6)
FROM facts | WHERE match(EXTRACT(MONTH FROM date_start),6)
FROM facts | WHERE EXTRACT(MONTH FROM date_start)=6

The closer one seems to be the one using DATEPART, where the error says:

Unknown function [DATEPART], did you mean any of [date_parse, date_format, date_extract, date_trunc]?

In the manual page I found Date/time and interval functions and operators | Reference it has that function, and not some of the alternatives presented.

[Tortoise]

Hello @pruna

I tried below and was able to extract the logs for particular month :

FROM kibana_sample_data_logs 
| WHERE DATE_EXTRACT("month_of_year", @timestamp) == 6

Thanks!!

[stephenb (Stephen Brown)]

Hi @pruna

You have to provide sample documents and mappings etc if you want help with a query otherwise it is just guessing on our part....

So provide some sample documents and their mapping and perhaps we can help.

[RainTown (Kevin Maguire)]

@pruna

In the manual page I found Date/time and interval functions and operators | Reference it has that function, and not some of the alternatives presented.

Note the /sql/ in the path there. That's SQL, not ES|QL, docs.

For ES|QL you can look at:

ES|QL date-time functions | Reference

Its not clear you level of Elastic knowledge here. If in early stages I would suggest to use Kibana --> Discover to build your knowledge - e.g. the link to the ES|QL docs is right there in kibana.

[pruna]

Thanks @Tortoise , @RainTown & stephenb (I can only mention 2 in the post) for your answers. It's amazing to see this level of participation. Trying to answer to you all:

That date query worked ok, thanks.

I've been using Elasticsearch for several years now, but always as a goal-oriented tool, so my knowledge is very limited to what I need at each time. I use it for an educational project I made (an interactive historical atlas) edumaps.ar .
I only use Elasticsearch, without Kibana or any other components from the stack.

As I wanted to add an anniversaries search, I started to look for a way to query only month & date pieces of a date, and that bring me into SQL, ES|QL, etc, which I didn't use before. If I can simply do it with traditional queries, I'll prefer that. Also because I see now that the output format is quite different.

About providing sample info, you are completely right, here would be some:

PUT /anniversaries
{
	"mappings": {
		"properties": {
			"id": { "type": "keyword" },
			"name": { "type": "text" },
			"date_start": { "type": "date" },
			"date_end": { "type": "date" }
		}
	}
}

POST /anniversaries/_bulk?pretty
{"index":{"_index":"anniversaries","_id":"1"}}
{"id":"1","name":"Juan","date_start":"1832-07-23","date_end":"1883-04-30"}
{"index":{"_index":"anniversaries","_id":"2"}}
{"id":"2","name":"Pablo","date_start":"1880-07-14","date_end":"1881-03-24"}
{"index":{"_index":"anniversaries","_id":"3"}}
{"id":"3","name":"Luis","date_start":"1581-07-14","date_end":"1599-09-03"}

POST /_query
{
    "query": "FROM anniversaries | WHERE DATE_EXTRACT(\"month_of_year\", date_start) == 7 AND DATE_EXTRACT(\"day_of_month\", date_start) == 14"
}

[RainTown (Kevin Maguire)]

thanks for clear answers/background.

One thing you might wish to consider is an ingest pipeline, which could extract the dayofweek/dayofmonth/etc fields at indexing time, populating those fields in your index. You would have to re-index existing data too, but ti would make queries more easy on the eye.

I'd also suggest to setup an instance of kibana, if only to test different things out. I think in long run it would save you time (e.g. kibana has autocompletion, inbuilt help, and IMO is just quicker once you are used to it.

From your shared data

FROM anniversaries
| EVAL monthOfYearStart=DATE_EXTRACT("month_of_year", date_start), monthOfYearEnd=DATE_EXTRACT("month_of_year", date_end), dayOfMonthStart=DATE_EXTRACT("day_of_month", date_start), dayOfMonthEnd=DATE_EXTRACT("day_of_month", date_end)
| WHERE monthOfYearStart == 7 AND dayOfMonthStart == 14

seems to me to do the job

( btw, someone more knowledgeable than me might know why kibana/ESQL part of Discover is displaying Luiz's date_start as 13th July 1581 at 23:58:45, and similar for date_end !? A little googling found that the Gregorian calendar was adopted in 1582, might be related )