Elasticsearch Sql and json files

[aeljazouli (Adil)]

Hey there !

Can Elasticsearch sql be used to query json files ?

[Andrei_Stefan]

Hi @aeljazouli,

ES SQL is for querying indices that exist in an Elasticsearch cluster. The query being used is translated to Elasticsearch query dsl that only looks at indices data. It doesn't query json files.

[aeljazouli (Adil)]

Hi @Andrei_Stefan,

I already create an index that contains json document like this :

{
"meta":{
"ID_FICHIER":127743,
"DATE_APPEL":"2019-06-06 10:22:42",
"SITE":"Casablanca"
},
"transcription":[
{
"confidence":0.985514,
"word":"energie"
},
{
"confidence":0.633511,
"word":"madame"
},
{
"confidence":0.533511,
"word":"pleine"
}
]
}

I don't know if i can use the ES SQL too to querry that data or just the lucene query language ?

[Andrei_Stefan]

@aeljazouli ES SQL is like standard SQL, with some limitations. So, whatever you can do in standard SQL, it should work in ES SQL. Again, with some limitations.

Also, an additional set of features, specific only to ES SQL are these three functions: https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-functions-search.html

And a very useful list of what functions and operators one can use in ES SQL, you can find here: https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-functions.html