Complex filter query

[Henri_L (HENRI LABARRE)]

Sorry if it might be obvious but I'm stuck with this query.

Here are some example input:

{
        "_index" : "education",
        "_type" : "logs",
        "_id" : "educ_0610426G",
        "_score" : 10.917585,
        "_source" : {
          "Code_departement" : 61,
          "Code_type_contrat_prive" : "99",
          "Academie" : "Normandie",
          "Identifiant_de_l_etablissement" : "0610426G",
          "update" : "sept2022",
          "Nom_commune" : "Crulai",
          "CODGEO" : "61140",
          "Type_contrat_prive" : "SANS OBJET",
          "provider" : "education",
          "tel" : "0233342401",
          "Code_region" : "28",
          "email" : "ce.0610426G@ac-caen.fr",
          "SIRET" : "21610140200024",
          "Type_etablissement" : "Ecole",
          "Statut_public_prive" : "Public",
          "Etat" : "OUVERT",
          "ULIS" : "0",
          "Restauration" : "1",
          "Code_academie" : "70",
          "cp" : "61300",
          "Hebergement" : "0",
          "Ecole_maternelle" : "1",
          "Nombre_d_eleves" : 64.0,
          "@timestamp" : "2022-09-16T15:11:51.145Z",
          "Nom_etablissement" : "Ecole primaire",
          "Ecole_elementaire" : "1",
          "location" : {
            "lon" : 0.6684494124470675,
            "lat" : 48.700301045165304
          },
          "Adresse_3" : "61300 CRULAI",
          "Adresse_1" : "4 rue d'Arpentigny"
        }
      },
{
        "_index" : "education",
        "_type" : "logs",
        "_id" : "educ_0610001V",
        "_score" : 13.178564,
        "_source" : {
          "Code_type_contrat_prive" : "99",
          "Section_internationale" : "0",
          "siteweb" : "http://lyceealainalencon.fr",
          "Nom_commune" : "Alençon",
          "CODGEO" : "61001",
          "Post_BAC" : "1",
          "Apprentissage" : "1",
          "tel" : "02 33 80 38 50",
          "Code_region" : "28",
          "fax" : "02 33 29 50 07",
          "Statut_public_prive" : "Public",
          "Section_arts" : "1",
          "Section_europeenne" : "1",
          "ULIS" : "0",
          "Segpa" : "0",
          "Restauration" : "1",
          "Lycee_des_metiers" : "0",
          "Voie_technologique" : "1",
          "Nom_etablissement" : "Lycée Alain",
          "Greta" : "0",
          "Adresse_2" : "BP 341",
          "Adresse_1" : "27 boulevard Mézeray",
          "Code_departement" : 61,
          "Academie" : "Normandie",
          "Identifiant_de_l_etablissement" : "0610001V",
          "update" : "sept2022",
          "Fiche_onisep" : "https://www.onisep.fr/http/redirection/etablissement/slug/ENS.13339",
          "Lycee_Agricole" : "0",
          "Type_contrat_prive" : "SANS OBJET",
          "provider" : "education",
          "Voie_generale" : "1",
          "Lycee_militaire" : "0",
          "email" : "ce.0610001v@ac-caen.fr",
          "SIRET" : "19610001000012",
          "Type_etablissement" : "Lycée",
          "Etat" : "OUVERT",
          "Voie_professionnelle" : "0",
          "Section_sport" : "0",
          "Code_academie" : "70",
          "cp" : "61014",
          "Section_theatre" : "0",
          "Hebergement" : "1",
          "Nombre_d_eleves" : 751.0,
          "@timestamp" : "2022-09-16T15:12:46.417Z",
          "location" : {
            "lon" : 0.08086951228482767,
            "lat" : 48.44021331062348
          },
          "Section_cinema" : "0"
        }

Hi want to query both Type_etablissement:Collège OR Type_etablissement:Lycée and to filter Type_etablissement:Lycée with "Voie_generale": "1" or "Voie_technologique": "1" which apply only to Type_etablissement:Lycée and not Type_etablissement:Collège

Any idea to build this query?

Thanks for your help

[Henri_L (HENRI LABARRE)]

Hi there,

I manage to make the query:

curl -XGET 'localhost:9200/education/_search?pretty' -H 'content-type: application/x-www-form-urlencoded; charset=UTF-8' -d'{
  "query":{
       "bool":{
           "should":
            [
               {"query_string" : {"query": "((Code_departement:14) AND (Ecole_maternelle:1 OR Ecole_elementaire:1) AND Type_etablissement:Ecole AND (NOT Nom_etablissement:\"A COMPLETER\") AND _exists_:Adresse_1 AND (NOT Adresse_1:\"CHEF LIEU CHEF LIEU\") AND (NOT Adresse_1:\"CHEF LIEU\") AND provider:education AND Etat:OUVERT AND update:sept2022)"}},
               {"query_string" : {"query": "((Code_departement:14) AND Type_etablissement:Collège AND (NOT Nom_etablissement:\"A COMPLETER\") AND _exists_:Adresse_1 AND (NOT Adresse_1:\"CHEF LIEU CHEF LIEU\") AND (NOT Adresse_1:\"CHEF LIEU\") AND provider:education AND Etat:OUVERT AND update:sept2022)"}},
               {"query_string" : {"query": "((Code_departement:14) AND (Voie_generale:1 OR Voie_technologique:1 OR Voie_professionnelle:1) AND Type_etablissement:Lycée AND (NOT Nom_etablissement:\"A COMPLETER\") AND _exists_:Adresse_1 AND (NOT Adresse_1:\"CHEF LIEU CHEF LIEU\") AND (NOT Adresse_1:\"CHEF LIEU\") AND provider:education AND Etat:OUVERT AND update:sept2022)"}}
            ],
            "minimum_should_match":1
        }
   }
}'