Example conversion from SQL to Query DSL

Elastic Stack Elasticsearch
1

Hiya

Structuring queries using the Query DSL is new to me. I've given a
simple MySQL example, which I've tried to translate into the Query
DSL.

Please can you tell me if I'm correct, and how I could make it
simpler, and/or more efficient.

thanks

Clint

SELECT      id
FROM        table
WHERE
            status              IN ('active','pending')
          , region               = 'south'
          , source               = 'south_main'
          , notice_type         IN ('family','sales')
          , MATCH("joe bloggs") AGAINST (name,text)
          , publish_date        BETWEEN '2010-01-01' AND

'2010-01-31'

ORDER BY    MATCH("joe bloggs") AGAINST (name,text)



{
 sort:  { score: {} },
 query: {
    filteredQuery: {
        query:  {
            disMax: {
                queries:    [
                    { queryString: {
                        query:          "joe bloggs",
                        defaultField:   "name",
                        boost:          2
                    }},
                    { queryString: {
                        query:          "joe bloggs",
                        defaultField:   "text",
                        boost:          1
                    }},
        }},
        filter: {
            bool: {
                must: [
                  { term: { region: "south" }},
                  { term: { source: "south_main" }},
                  { disMax: {
                        queries:    [
                            { term: { notice_type: "family" }},
                            { term: { notice_type: "sales" }}
                        ]
                  }},
                  { range: {
                        publish_date: {
                            from:           "2010-01-01",
                            to:             "2010-01-31",
                            includeLower:   true,
                            inludeUpper:

true
}
}}
],
}}
}
}}

2

Hello @Clinton_Gormley

I know that your post is old but the following answer could be useful for someone else because this topic was the top Google answer. I was searching the same thing than you and found out this gem : https://github.com/NLPchina/elasticsearch-sql/issues/156. I don't know if it could solve someone else problem or even how you solved yours but it's still a good alternative.

An announcement has even been released here : Announcment: elasticsearch-sql release version 1.2.

Long live ES.