0

Sql query is a very fantastic tool offered by EL 7.3. However i cant explain that bug :

When i use SUM with Match , 1 condition, query below works well

POST _xpack/sql?format=txt
{
"query":"SELECT SUM(FlightTimeHour) Avg_Flight_Time FROM flights where MATCH(OriginCountry,'AE') "
}

But if i use 2 or more conditions, SUM and MATCH query below fails :

POST _xpack/sql?format=txt
{
"query":"SELECT SUM(FlightTimeHour) Avg_Flight_Time FROM flights where MATCH(OriginCountry,'AE') OR MATCH(OriginCountry,'FR') "
}

Can someone tell me what is wrong ?

Johna Pop
  • 11
  • 4

1 Answers1

0

SQL documentation mentions nothing about boolean operators on MATCH. I don't think it is supported.

You can perform above search as below 1. using query instead of Match for full text search

"query": "SELECT SUM(FlightTimeHour) Avg_Flight_Time FROM flights where  QUERY('OriginCountry: AE OR OriginCountry: FR')"
  1. Perform search on keywords
"query": "SELECT SUM(FlightTimeHour) Avg_Flight_Time FROM flights where OriginCountry.keyword='AE' OR OriginCountry.keyword='FR'"

EDIT: Above query as elastic search

GET fonds/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "multi_match": {
            "query": "bordeaux",
            "fields": [
              "IDEE",
              "SLOGAN",
              "NOM",
              "ADRESSE",
              "VILLE",
              "ACTIVITE1",
              "ACTIVITE2",
              "KEYWORDS",
              "KEYWORDS_SITE",
              "SITE_H1_H6",
              "DESCRIPTION",
              "DESCRIPTION_SITE",
              "ACTIVITE3",
              "BUSINESS_MODEL",
              "COLORS"
            ]
          }
        },
        {
          "range": {
            "FONDS_LEVEES_TOTAL": {
              "gt": 0
            }
          } 
        },
        {
          "script": {
            "script": "doc['COMPE_RESULTAT_CA_2000'].value + doc['COMPE_RESULTAT_CA_2001'].value>0"
          }
        }
      ]
    }
  },
  "aggs": {
    "SUM": {
      "sum": {
        "field": "FONDS_LEVEES_TOTAL"
      }
    }
  }
}
jaspreet chahal
  • 8,817
  • 2
  • 11
  • 29
  • Thanks option 1 works well but its impossible to combine for example QUERY('fieldname1: value1 OR fieldname2: value2 ) AND YEAR_CREATION>2010 – Johna Pop Sep 30 '19 at 13:08
  • "query": "SELECT SUM(FlightTimeHour) Avg_Flight_Time FROM flights where QUERY('(OriginCountry: AE OR OriginCountry: FR) AND YEAR_CREATION:>2010')" --- you need to add year creation inside query and look for :> syntax – jaspreet chahal Sep 30 '19 at 13:29
  • https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-functions-search.html#sql-functions-search-query has several example of using query. Both query and match are used for full text search so if you dont need partial match etc you can avoid complexity and use option 2 – jaspreet chahal Sep 30 '19 at 13:31
  • Thanks, but the problem is i have to combine QUERY('(OriginCountry: AE OR OriginCountry: FR) AND YEAR_CREATION>2010')" with aggregation, and no way to make it work. `code` SELECT SUM(*) FROM INDEX WHERE QUERY('(OriginCountry: AE OR OriginCountry: FR) AND YEAR_CREATION>2010') – Johna Pop Sep 30 '19 at 14:28
  • QUERY('(OriginCountry: AE OR OriginCountry: FR) AND YEAR_CREATION:>2010')" (works) is equvalent to OriginCountry: AE OR OriginCountry: FR) AND YEAR_CREATION>2010'). Can you please elaborate how it is not working for you – jaspreet chahal Sep 30 '19 at 14:38
  • Sorry for the length of the code , here is my code above : SELECT SUM(FONDS_LEVEES_TOTAL) as FONDS FROM baz_bizz WHERE ( QUERY('IDEE:bordeaux OR SLOGAN:bordeaux OR NOM:bordeaux OR ADRESSE:bordeaux OR VILLE:bordeaux OR ACTIVITE1:bordeaux OR ACTIVITE2:bordeaux OR KEYWORDS:bordeaux OR KEYWORDS_SITE:bordeaux OR SITE_H1_H6:bordeaux OR DESCRIPTION:bordeaux OR DESCRIPTION_SITE:bordeaux OR ACTIVITE3:bordeaux OR BUSINESS_MODEL:bordeaux OR COLORS:bordeaux ')) AND ((COMPE_RESULTAT_CA_2000 + COMPE_RESULTAT_CA_2001 2 > 0) AND (FONDS_LEVEES_TOTAL > 0)) – Johna Pop Sep 30 '19 at 15:03
  • above query can be written in QUERY except (COMPE_RESULTAT_CA_2000 + COMPE_RESULTAT_CA_2001 2 > 0 .If this was a single field (changes will be needed in mapping for that) then you could have moved it inside "QUERY". You can remove query to achieve . I believe you are searching for exact words so you can either store fields as keyword type or create one and run this in normal where clause(option2 in my answer ) – jaspreet chahal Oct 01 '19 at 03:48
  • thanks but no solution for my problem, i use for text and keyword for all my text fields, INT for all my numeric fields. Im using elastic search 7.3. It seems elastic is not easy for simple SQL queries like this SUM with multiple conditions (text keywords conditions combined with mathematical conditions. ), without SUM the sql query work fine, but when i add aggregation, it fails... – Johna Pop Oct 02 '19 at 18:44
  • I have added elastic search implementation of above sql query under EDIT. Have a check if it serves your purpose – jaspreet chahal Oct 07 '19 at 11:33