Query and sub query

[lfourny (Laurentf60)]

Hello everyone,
I am trying to translate in elasticsearch 'a sql query like the following example:

SELECT numero_serie, Qte_errors
FROM
(
Select
numero_serie as numero_serie,
count(id) as Qte_errors
FROM IndexErrors
WHERE numero_serie IN ( 'GA001', 'GG016', 'GK101', 'GI028','GK040','GL032')
AND error_code IN ('MOT_A1', 'GEN_C8', 'COL_D5','COL_Z8','GEN_B7')
AND (error_date BETWEEN '2019-03-15 00:00:00' AND '2019-03-18 23:59:00')
GROUP BY numero_serie
) AS Results
WHERE Qte_errors >= 2;

Is it possible ?

[Andrei_Stefan]

@lfourny have you tried Elasticsearch SQL's Translate API with the query above? A quick look at the sub-select, I think it should work. I don't recommend running the query exactly as is, because sub-selects are mostly unsupported, but you could try something like the following (and I think it's equivalent to your bigger query):

SELECT
numero_serie AS numero_serie,
COUNT(id) AS Qte_errors
FROM IndexErrors
WHERE numero_serie IN ('GA001', 'GG016', 'GK101', 'GI028','GK040','GL032')
             AND error_code IN ('MOT_A1', 'GEN_C8', 'COL_D5','COL_Z8','GEN_B7')
             AND (error_date BETWEEN '2019-03-15 00:00:00' AND '2019-03-18 23:59:00')
GROUP BY numero_serie
HAVING Qte_errors >= 2;

The translate API will give you a query using some scripts we use internally for the HAVING part, but you could come up with your own Painless scripting instead of those, based on what the translate API gives you (should be easy to adapt).