# How to translate “and or” where clause from sql query to elasticsearch filter **URL:** \ **Category:** Elasticsearch\ **Created:** [July 10, 2014, 9:15am UTC](https://discuss.elastic.co/t/how-to-translate-and-or-where-clause-from-sql-query-to-elasticsearch-filter/18572 "2014-07-10T09:15:34Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![Artem\_Frolov](https://avatars.discourse-cdn.com/v4/letter/a/f04885/32.png) [@Artem\_Frolov](https://discuss.elastic.co/u/Artem_Frolov)\ **Post date:** [July 10, 2014, 9:15am UTC](https://discuss.elastic.co/t/how-to-translate-and-or-where-clause-from-sql-query-to-elasticsearch-filter/18572/1 "2014-07-10T09:15:34Z")
I have a WHERE clause in my SQL query, which have to be translated into the elasticsearch bool filter. here's the where clause: WHERE (option = "weight" AND value = "50kg")OR (option = "weight" AND value = "500kg")AND (option = "magic" AND value = "no") I have written the AND filters for inner ANDs of query, but now I need to put them to the bool filter. Tried to: $boolFilter = new \Elastica\Filter\Bool();$boolFilter-\>addShould($innerFilterAnd1);$boolFilter-\>addShould($innerFilterAnd2);$boolFilter-\>addMust($innerFilterAnd3); returns nothing. Please, help! -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [July 10, 2014, 9:22am UTC](https://discuss.elastic.co/t/how-to-translate-and-or-where-clause-from-sql-query-to-elasticsearch-filter/18572/2 "2014-07-10T09:22:30Z")
It could help if you could gist a full SENSE/curl script recreation Best -- David 😉 Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs > Le 10 juil. 2014 à 11:15, Artem Frolov [keem23@gmail.com](mailto:keem23@gmail.com) a écrit : > > I have a WHERE clause in my SQL query, which have to be translated into the elasticsearch bool filter. > > here's the where clause: > > WHERE ( > option = "weight" AND value = "50kg" > ) > OR ( > option = "weight" AND value = "500kg" > ) > AND ( > option = "magic" AND value = "no" > ) > I have written the AND filters for inner ANDs of query, but now I need to put them to the bool filter. > > Tried to: > > $boolFilter = new \Elastica\Filter\Bool(); > $boolFilter-\>addShould($innerFilterAnd1); > $boolFilter-\>addShould($innerFilterAnd2); > $boolFilter-\>addMust($innerFilterAnd3); > returns nothing. > > Please, help! > > -- > You received this message because you are subscribed to the Google Groups "elasticsearch" group. > To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com). > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/6B17E7B5-31DE-43CE-8ABD-26D8FD668800%40pilato.fr](https://groups.google.com/d/msgid/elasticsearch/6B17E7B5-31DE-43CE-8ABD-26D8FD668800%40pilato.fr). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Artem\_Frolov](https://avatars.discourse-cdn.com/v4/letter/a/f04885/32.png) [@Artem\_Frolov](https://discuss.elastic.co/u/Artem_Frolov)\ **Post date:** [July 10, 2014, 9:51am UTC](https://discuss.elastic.co/t/how-to-translate-and-or-where-clause-from-sql-query-to-elasticsearch-filter/18572/3 "2014-07-10T09:51:48Z")
here's my try to solve it: { "filtered":{ "filter":{ "nested":{ "path":"productsOptionValues", "filter":{ "and":[ { "or":[ { "and":[ { "term":{ "productsOptionValues.productOption": "weight" } }, { "term":{ "productsOptionValues.value":"500 kg" } } ] }, { "and":[ { "term":{ "productsOptionValues.productOption": "weight" } }, { "term":{ "productsOptionValues.value":"50kg" } } ] } ] }, { "or":[ { "and":[ { "term":{ "productsOptionValues.productOption": "magic" } }, { "term":{ "productsOptionValues.value":"no" } } ] } ] } ] } } } } } but it's an equivalent of: WHERE ((option = "weight" AND value = "50kg")OR (option = "weight" AND value = "500kg"))AND (option = "magic" AND value = "no") it's wrong tree... I need the and\or logic to be at one branch of the json tree. I don't know if it possible. Please help to translate the logic from the query where condition! четверг, 10 июля 2014 г., 12:23:05 UTC+3 пользователь David Pilato написал: > It could help if you could gist a full SENSE/curl script recreation > > Best > > -- > David 😉 > Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs > > Le 10 juil. 2014 à 11:15, Artem Frolov \<[kee...@gmail.com](mailto:kee...@gmail.com) \\> a > écrit : > > I have a WHERE clause in my SQL query, which have to be translated into > the elasticsearch bool filter. > > here's the where clause: > > WHERE (option = "weight" AND value = "50kg")OR (option = "weight" AND value = "500kg")AND (option = "magic" AND value = "no") > > I have written the AND filters for inner ANDs of query, but now I need to > put them to the bool filter. > > Tried to: > > $boolFilter = new \Elastica\Filter\Bool();$boolFilter-\>addShould($innerFilterAnd1);$boolFilter-\>addShould($innerFilterAnd2);$boolFilter-\>addMust($innerFilterAnd3); > > returns nothing. > > Please, help! > > -- > You received this message because you are subscribed to the Google Groups > "elasticsearch" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com) \. > To view this discussion on the web visit > [https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com) > [https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com?utm_medium=email&utm_source=footer) > . > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/41f906b1-ed46-4f37-b3f3-d932202f88ef%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/41f906b1-ed46-4f37-b3f3-d932202f88ef%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Artem\_Frolov](https://avatars.discourse-cdn.com/v4/letter/a/f04885/32.png) [@Artem\_Frolov](https://discuss.elastic.co/u/Artem_Frolov)\ **Post date:** [July 10, 2014, 9:59am UTC](https://discuss.elastic.co/t/how-to-translate-and-or-where-clause-from-sql-query-to-elasticsearch-filter/18572/4 "2014-07-10T09:59:17Z")
the GIST: > четверг, 10 июля 2014 г., 12:51:49 UTC+3 пользователь Artem Frolov написал: > here's my try to solve it: > > { > "filtered":{ > "filter":{ > "nested":{ > "path":"productsOptionValues", > "filter":{ > "and":[ > { > "or":[ > { > "and":[ > { > "term":{ > "productsOptionValues.productOption": > "weight" > } > }, > { > "term":{ > "productsOptionValues.value":"500 kg" > } > } > ] > }, > { > "and":[ > { > "term":{ > "productsOptionValues.productOption": > "weight" > } > }, > { > "term":{ > "productsOptionValues.value":"50kg" > } > } > ] > } > ] > }, > { > "or":[ > { > "and":[ > { > "term":{ > "productsOptionValues.productOption": > "magic" > } > }, > { > "term":{ > "productsOptionValues.value":"no" > } > } > ] > } > ] > } > ] > } > } > } > } > } > > but it's an equivalent of: > > WHERE ((option = "weight" AND value = "50kg")OR (option = "weight" AND value = "500kg"))AND (option = "magic" AND value = "no") > > it's wrong tree... I need the and\or logic to be at one branch of the json tree. I don't know if it possible. Please help to translate the logic from the query where condition! > > четверг, 10 июля 2014 г., 12:23:05 UTC+3 пользователь David Pilato написал: > > > It could help if you could gist a full SENSE/curl script recreation > > > > Best > > > > -- > > David 😉 > > Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs > > > > Le 10 juil. 2014 à 11:15, Artem Frolov [kee...@gmail.com](mailto:kee...@gmail.com) a écrit : > > > > I have a WHERE clause in my SQL query, which have to be translated into > > the elasticsearch bool filter. > > > > here's the where clause: > > > > WHERE (option = "weight" AND value = "50kg")OR (option = "weight" AND value = "500kg")AND (option = "magic" AND value = "no") > > > > I have written the AND filters for inner ANDs of query, but now I need to > > put them to the bool filter. > > > > Tried to: > > > > $boolFilter = new \Elastica\Filter\Bool();$boolFilter-\>addShould($innerFilterAnd1);$boolFilter-\>addShould($innerFilterAnd2);$boolFilter-\>addMust($innerFilterAnd3); > > > > returns nothing. > > > > Please, help! > > > > -- > > You received this message because you are subscribed to the Google Groups > > "elasticsearch" group. > > To unsubscribe from this group and stop receiving emails from it, send an > > email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). > > To view this discussion on the web visit > > [https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com) > > [https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com?utm_medium=email&utm_source=footer) > > . > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/70db39db-2317-46bc-a2a9-6011f454b46d%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/70db39db-2317-46bc-a2a9-6011f454b46d%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [July 10, 2014, 3:37pm UTC](https://discuss.elastic.co/t/how-to-translate-and-or-where-clause-from-sql-query-to-elasticsearch-filter/18572/5 "2014-07-10T15:37:05Z")
Have a look at this page to see how you can build a full working GIST which could help us to reproduce your use case. When your GIST will be updated, please update this thread so I can look at it. -- David Pilato | Technical Advocate | [Elasticsearch.com](http://Elasticsearch.com) @dadoonet | @elasticsearchfr Le 10 juillet 2014 à 11:59:22, Artem Frolov ([keem23@gmail.com](mailto:keem23@gmail.com)) a écrit: the GIST: > четверг, 10 июля 2014 г., 12:51:49 UTC+3 пользователь Artem Frolov написал: here's my try to solve it: { "filtered":{ "filter":{ "nested":{ "path":"productsOptionValues", "filter":{ "and":[ { "or":[ { "and":[ { "term":{ "productsOptionValues.productOption":"weight" } }, { "term":{ "productsOptionValues.value":"500 kg" } } ] }, { "and":[ { "term":{ "productsOptionValues.productOption":"weight" } }, { "term":{ "productsOptionValues.value":"50kg" } } ] } ] }, { "or":[ { "and":[ { "term":{ "productsOptionValues.productOption":"magic" } }, { "term":{ "productsOptionValues.value":"no" } } ] } ] } ] } } } } } but it's an equivalent of: WHERE (( option = "weight" AND value = "50kg" ) OR ( option = "weight" AND value = "500kg" )) AND ( option = "magic" AND value = "no" ) it's wrong tree... I need the and\or logic to be at one branch of the json tree. I don't know if it possible. Please help to translate the logic from the query where condition! четверг, 10 июля 2014 г., 12:23:05 UTC+3 пользователь David Pilato написал: It could help if you could gist a full SENSE/curl script recreation Best -- David 😉 Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs Le 10 juil. 2014 à 11:15, Artem Frolov [kee...@gmail.com](mailto:kee...@gmail.com) a écrit : I have a WHERE clause in my SQL query, which have to be translated into the elasticsearch bool filter. here's the where clause: WHERE ( option = "weight" AND value = "50kg" ) OR ( option = "weight" AND value = "500kg" ) AND ( option = "magic" AND value = "no" ) I have written the AND filters for inner ANDs of query, but now I need to put them to the bool filter. Tried to: $boolFilter = new \Elastica\Filter\Bool(); $boolFilter-\>addShould($innerFilterAnd1); $boolFilter-\>addShould($innerFilterAnd2); $boolFilter-\>addMust($innerFilterAnd3); returns nothing. Please, help! ## -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/70db39db-2317-46bc-a2a9-6011f454b46d%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/70db39db-2317-46bc-a2a9-6011f454b46d%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/etPan.53beb321.66334873.7c8b%40MacBook-Air-de-David.local](https://groups.google.com/d/msgid/elasticsearch/etPan.53beb321.66334873.7c8b%40MacBook-Air-de-David.local). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Artem\_Frolov](https://avatars.discourse-cdn.com/v4/letter/a/f04885/32.png) [@Artem\_Frolov](https://discuss.elastic.co/u/Artem_Frolov)\ **Post date:** [July 11, 2014, 8:43am UTC](https://discuss.elastic.co/t/how-to-translate-and-or-where-clause-from-sql-query-to-elasticsearch-filter/18572/6 "2014-07-11T08:43:56Z")
Can you answer what's wrong with the gist, i've provided? I can't figure out... I provide you all the data I have, related to this issue. Look: 1. The elasticsearch index, containing the products and its options, which have to be filtered: [gist:de86b8b0a5f2bc7dfd86 · GitHub](https://gist.github.com/ArFeRR/de86b8b0a5f2bc7dfd86) 2. The JSON query for the filtration: [gist:e159ef1047122a617b88 · GitHub](https://gist.github.com/ArFeRR/e159ef1047122a617b88) 3)The [ELastica.io](http://ELastica.io) code to genereate the json above on PHP: [gist:cebb2bf54232069d817b · GitHub](https://gist.github.com/ArFeRR/cebb2bf54232069d817b#file-gistfile1-php) (I've changed the data to make it more real) All the json works fine, i've tested it! Can't figure out what's wrong and what I have to do to provide a "FULL working GIST" for you четверг, 10 июля 2014 г., 18:37:19 UTC+3 пользователь David Pilato написал: > Have a look at this page to see how you can build a full working GIST > which could help us to reproduce your use case. > > When your GIST will be updated, please update this thread so I can look at > it. > > -- > _David Pilato_ | _Technical Advocate_ | _[Elasticsearch.com](http://Elasticsearch.com)_ > @dadoonet [https://twitter.com/dadoonet](https://twitter.com/dadoonet) | @elasticsearchfr > [https://twitter.com/elasticsearchfr](https://twitter.com/elasticsearchfr) > > Le 10 juillet 2014 à 11:59:22, Artem Frolov ([kee...@gmail.com](mailto:kee...@gmail.com) > \) a écrit: > > the GIST: > [gist:630acb216b8d95168b73 · GitHub](https://gist.github.com/ArFeRR/630acb216b8d95168b73) > > четверг, 10 июля 2014 г., 12:51:49 UTC+3 пользователь Artem Frolov > написал: > > > here's my try to solve it: > > > > { > > "filtered":{ > > "filter":{ > > "nested":{ > > "path":"productsOptionValues", > > "filter":{ > > "and":[ > > { > > "or":[ > > { > > "and":[ > > { > > "term":{ > > "productsOptionValues.productOption": > > "weight" > > } > > }, > > { > > "term":{ > > "productsOptionValues.value":"500 kg" > > } > > } > > ] > > }, > > { > > "and":[ > > { > > "term":{ > > "productsOptionValues.productOption": > > "weight" > > } > > }, > > { > > "term":{ > > "productsOptionValues.value":"50kg" > > } > > } > > ] > > } > > ] > > }, > > { > > "or":[ > > { > > "and":[ > > { > > "term":{ > > "productsOptionValues.productOption": > > "magic" > > } > > }, > > { > > "term":{ > > "productsOptionValues.value":"no" > > } > > } > > ] > > } > > ] > > } > > ] > > } > > } > > } > > } > > } > > > > but it's an equivalent of: > > > > WHERE ((option = "weight" AND value = "50kg")OR (option = "weight" AND value = "500kg"))AND (option = "magic" AND value = "no") > > > > it's wrong tree... I need the and\or logic to be at one branch of the json tree. I don't know if it possible. Please help to translate the logic from the query where condition! > > > > четверг, 10 июля 2014 г., 12:23:05 UTC+3 пользователь David Pilato > > написал: > > > > > It could help if you could gist a full SENSE/curl script recreation > > > > > > Best > > > > > > -- > > > David 😉 > > > Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs > > > > > > Le 10 juil. 2014 à 11:15, Artem Frolov [kee...@gmail.com](mailto:kee...@gmail.com) a écrit : > > > > > > I have a WHERE clause in my SQL query, which have to be translated > > > into the elasticsearch bool filter. > > > > > > here's the where clause: > > > > > > WHERE (option = "weight" AND value = "50kg")OR (option = "weight" AND value = "500kg")AND (option = "magic" AND value = "no") > > > > > > I have written the AND filters for inner ANDs of query, but now I need > > > to put them to the bool filter. > > > > > > Tried to: > > > > > > $boolFilter = new \Elastica\Filter\Bool();$boolFilter-\>addShould($innerFilterAnd1);$boolFilter-\>addShould($innerFilterAnd2);$boolFilter-\>addMust($innerFilterAnd3); > > > > > > returns nothing. > > > > > > ## Please, help! > > > > > > You received this message because you are subscribed to the Google > > > Groups "elasticsearch" group. > > > To unsubscribe from this group and stop receiving emails from it, send > > > an email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). > > > To view this discussion on the web visit > > > [https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com) > > > [https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com?utm_medium=email&utm_source=footer) > > > . > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > > > > > -- > > > You received this message because you are subscribed to the Google Groups > > > "elasticsearch" group. > > > To unsubscribe from this group and stop receiving emails from it, send an > > > email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com) \. > > > To view this discussion on the web visit > > > [https://groups.google.com/d/msgid/elasticsearch/70db39db-2317-46bc-a2a9-6011f454b46d%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/70db39db-2317-46bc-a2a9-6011f454b46d%40googlegroups.com) > > > [https://groups.google.com/d/msgid/elasticsearch/70db39db-2317-46bc-a2a9-6011f454b46d%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/70db39db-2317-46bc-a2a9-6011f454b46d%40googlegroups.com?utm_medium=email&utm_source=footer) > > > . > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/1ee83ee3-09a6-414f-87fe-faf9f97d06a6%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/1ee83ee3-09a6-414f-87fe-faf9f97d06a6%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [July 11, 2014, 8:48am UTC](https://discuss.elastic.co/t/how-to-translate-and-or-where-clause-from-sql-query-to-elasticsearch-filter/18572/7 "2014-07-11T08:48:23Z")
A full script would allow any user on the mailing list to recreate from scratch your issue without the need of building a script by ourselves which is really time consuming. So, basically a script should look like this: // Remove test data DELETE test // If needed, add your settings/mappings PUT test { "settings": {}, "mappings": {} } // Index some data PUT test/doc/1 { "foo":"bar" } PUT test/doc/x?refresh { "foo":"bar" } // Run the query GET test/doc/\_search { } With that, we can definitely help you I think. -- David Pilato | Technical Advocate | [Elasticsearch.com](http://Elasticsearch.com) @dadoonet | @elasticsearchfr Le 11 juillet 2014 à 10:44:01, Artem Frolov ([keem23@gmail.com](mailto:keem23@gmail.com)) a écrit: Can you answer what's wrong with the gist, i've provided? I can't figure out... I provide you all the data I have, related to this issue. Look: 1. The elasticsearch index, containing the products and its options, which have to be filtered: [https://gist.github.com/ArFeRR/de86b8b0a5f2bc7dfd86](https://gist.github.com/ArFeRR/de86b8b0a5f2bc7dfd86) 2. The JSON query for the filtration: [https://gist.github.com/ArFeRR/e159ef1047122a617b88](https://gist.github.com/ArFeRR/e159ef1047122a617b88) 3)The ELastica.io code to genereate the json above on PHP: [https://gist.github.com/ArFeRR/cebb2bf54232069d817b#file-gistfile1-php](https://gist.github.com/ArFeRR/cebb2bf54232069d817b#file-gistfile1-php) (I've changed the data to make it more real) All the json works fine, i've tested it! Can't figure out what's wrong and what I have to do to provide a "FULL working GIST" for you четверг, 10 июля 2014 г., 18:37:19 UTC+3 пользователь David Pilato написал: Have a look at this page to see how you can build a full working GIST which could help us to reproduce your use case. When your GIST will be updated, please update this thread so I can look at it. -- David Pilato | Technical Advocate | [Elasticsearch.com](http://Elasticsearch.com) @dadoonet | @elasticsearchfr Le 10 juillet 2014 à 11:59:22, Artem Frolov ([kee...@gmail.com](mailto:kee...@gmail.com)) a écrit: the GIST: > четверг, 10 июля 2014 г., 12:51:49 UTC+3 пользователь Artem Frolov написал: here's my try to solve it: { "filtered":{ "filter":{ "nested":{ "path":"productsOptionValues", "filter":{ "and":[ { "or":[ { "and":[ { "term":{ "productsOptionValues.productOption":"weight" } }, { "term":{ "productsOptionValues.value":"500 kg" } } ] }, { "and":[ { "term":{ "productsOptionValues.productOption":"weight" } }, { "term":{ "productsOptionValues.value":"50kg" } } ] } ] }, { "or":[ { "and":[ { "term":{ "productsOptionValues.productOption":"magic" } }, { "term":{ "productsOptionValues.value":"no" } } ] } ] } ] } } } } } but it's an equivalent of: WHERE (( option = "weight" AND value = "50kg" ) OR ( option = "weight" AND value = "500kg" )) AND ( option = "magic" AND value = "no" ) it's wrong tree... I need the and\or logic to be at one branch of the json tree. I don't know if it possible. Please help to translate the logic from the query where condition! четверг, 10 июля 2014 г., 12:23:05 UTC+3 пользователь David Pilato написал: It could help if you could gist a full SENSE/curl script recreation Best -- David 😉 Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs Le 10 juil. 2014 à 11:15, Artem Frolov [kee...@gmail.com](mailto:kee...@gmail.com) a écrit : I have a WHERE clause in my SQL query, which have to be translated into the elasticsearch bool filter. here's the where clause: WHERE ( option = "weight" AND value = "50kg" ) OR ( option = "weight" AND value = "500kg" ) AND ( option = "magic" AND value = "no" ) I have written the AND filters for inner ANDs of query, but now I need to put them to the bool filter. Tried to: $boolFilter = new \Elastica\Filter\Bool(); $boolFilter-\>addShould($innerFilterAnd1); $boolFilter-\>addShould($innerFilterAnd2); $boolFilter-\>addMust($innerFilterAnd3); returns nothing. Please, help! ## -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ## You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/70db39db-2317-46bc-a2a9-6011f454b46d%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/70db39db-2317-46bc-a2a9-6011f454b46d%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/1ee83ee3-09a6-414f-87fe-faf9f97d06a6%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/1ee83ee3-09a6-414f-87fe-faf9f97d06a6%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/etPan.53bfa4d7.41b71efb.70e%40MacBook-Air-de-David.local](https://groups.google.com/d/msgid/elasticsearch/etPan.53bfa4d7.41b71efb.70e%40MacBook-Air-de-David.local). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Artem\_Frolov](https://avatars.discourse-cdn.com/v4/letter/a/f04885/32.png) [@Artem\_Frolov](https://discuss.elastic.co/u/Artem_Frolov)\ **Post date:** [July 11, 2014, 2:15pm UTC](https://discuss.elastic.co/t/how-to-translate-and-or-where-clause-from-sql-query-to-elasticsearch-filter/18572/8 "2014-07-11T14:15:04Z")
I've done what you want. Mapping and populating data GIST: > GIST with my search query: > (it have to return one record with resolution:1920x1080 and weight: 2,9 kg. It's the notebook with the name "Lenovo IdeaPad Z710A") but it returns empty. Please help to achieve the behaviour what i need! пятница, 11 июля 2014 г., 11:48:40 UTC+3 пользователь David Pilato написал: > A full script would allow any user on the mailing list to recreate from > scratch your issue without the need of building a script by ourselves which > is really time consuming. > > So, basically a script should look like this: > > // Remove test data > DELETE test > > // If needed, add your settings/mappings > PUT test > { > "settings": {}, > "mappings": {} > } > > // Index some data > PUT test/doc/1 > { > "foo":"bar" > } > > PUT test/doc/x?refresh > { > "foo":"bar" > } > > // Run the query > GET test/doc/\_search > { > } > > With that, we can definitely help you I think. > > -- > _David Pilato_ | _Technical Advocate_ | _[Elasticsearch.com](http://Elasticsearch.com)_ > @dadoonet [https://twitter.com/dadoonet](https://twitter.com/dadoonet) | @elasticsearchfr > [https://twitter.com/elasticsearchfr](https://twitter.com/elasticsearchfr) > > Le 11 juillet 2014 à 10:44:01, Artem Frolov ([kee...@gmail.com](mailto:kee...@gmail.com) > \) a écrit: > > Can you answer what's wrong with the gist, i've provided? I can't figure > out... > I provide you all the data I have, related to this issue. Look: > > 1. The elasticsearch index, containing the products and its options, which > have to be filtered: [gist:de86b8b0a5f2bc7dfd86 · GitHub](https://gist.github.com/ArFeRR/de86b8b0a5f2bc7dfd86) > 2. The JSON query for the filtration: > [gist:e159ef1047122a617b88 · GitHub](https://gist.github.com/ArFeRR/e159ef1047122a617b88) > 3)The [ELastica.io](http://ELastica.io) code to genereate the json above on PHP: > [gist:cebb2bf54232069d817b · GitHub](https://gist.github.com/ArFeRR/cebb2bf54232069d817b#file-gistfile1-php) > > (I've changed the data to make it more real) > > All the json works fine, i've tested it! Can't figure out what's wrong and > what I have to do to provide a "FULL working GIST" for you > > четверг, 10 июля 2014 г., 18:37:19 UTC+3 пользователь David Pilato > написал: > > > Have a look at this page to see how you can build a full working GIST > > which could help us to reproduce your use case. > > > > When your GIST will be updated, please update this thread so I can look > > at it. > > > > ``` > > -- > > > > ``` > > > > _David Pilato_ | _Technical Advocate_ | _[Elasticsearch.com](http://Elasticsearch.com)_ > > @dadoonet [https://twitter.com/dadoonet](https://twitter.com/dadoonet) | @elasticsearchfr > > [https://twitter.com/elasticsearchfr](https://twitter.com/elasticsearchfr) > > > > Le 10 juillet 2014 à 11:59:22, Artem Frolov ([kee...@gmail.com](mailto:kee...@gmail.com)) a écrit: > > > > the GIST: > > [gist:630acb216b8d95168b73 · GitHub](https://gist.github.com/ArFeRR/630acb216b8d95168b73) > > > > четверг, 10 июля 2014 г., 12:51:49 UTC+3 пользователь Artem Frolov > > написал: > > > > > here's my try to solve it: > > > > > > { > > > "filtered":{ > > > "filter":{ > > > "nested":{ > > > "path":"productsOptionValues", > > > "filter":{ > > > "and":[ > > > { > > > "or":[ > > > { > > > "and":[ > > > { > > > "term":{ > > > "productsOptionValues.productOption" > > > :"weight" > > > } > > > }, > > > { > > > "term":{ > > > "productsOptionValues.value": > > > "500 kg" > > > } > > > } > > > ] > > > }, > > > { > > > "and":[ > > > { > > > "term":{ > > > "productsOptionValues.productOption" > > > :"weight" > > > } > > > }, > > > { > > > "term":{ > > > "productsOptionValues.value":"50kg" > > > } > > > } > > > ] > > > } > > > ] > > > }, > > > { > > > "or":[ > > > { > > > "and":[ > > > { > > > "term":{ > > > "productsOptionValues.productOption" > > > :"magic" > > > } > > > }, > > > { > > > "term":{ > > > "productsOptionValues.value":"no" > > > } > > > } > > > ] > > > } > > > ] > > > } > > > ] > > > } > > > } > > > } > > > } > > > } > > > > > > but it's an equivalent of: > > > > > > WHERE ((option = "weight" AND value = "50kg")OR (option = "weight" AND value = "500kg"))AND (option = "magic" AND value = "no") > > > > > > it's wrong tree... I need the and\or logic to be at one branch of the json tree. I don't know if it possible. Please help to translate the logic from the query where condition! > > > > > > четверг, 10 июля 2014 г., 12:23:05 UTC+3 пользователь David Pilato > > > написал: > > > > > > > It could help if you could gist a full SENSE/curl script recreation > > > > > > > > Best > > > > > > > > -- > > > > David 😉 > > > > Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs > > > > > > > > Le 10 juil. 2014 à 11:15, Artem Frolov [kee...@gmail.com](mailto:kee...@gmail.com) a écrit : > > > > > > > > I have a WHERE clause in my SQL query, which have to be translated > > > > into the elasticsearch bool filter. > > > > > > > > here's the where clause: > > > > > > > > WHERE (option = "weight" AND value = "50kg")OR (option = "weight" AND value = "500kg")AND (option = "magic" AND value = "no") > > > > > > > > I have written the AND filters for inner ANDs of query, but now I need > > > > to put them to the bool filter. > > > > > > > > Tried to: > > > > > > > > $boolFilter = new \Elastica\Filter\Bool();$boolFilter-\>addShould($innerFilterAnd1);$boolFilter-\>addShould($innerFilterAnd2);$boolFilter-\>addMust($innerFilterAnd3); > > > > > > > > returns nothing. > > > > > > > > ## Please, help! > > > > > > > > You received this message because you are subscribed to the Google > > > > Groups "elasticsearch" group. > > > > To unsubscribe from this group and stop receiving emails from it, send > > > > an email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). > > > > To view this discussion on the web visit > > > > [https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com) > > > > [https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com?utm_medium=email&utm_source=footer) > > > > . > > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > > > > > > > -- > > > > You received this message because you are subscribed to the Google Groups > > > > "elasticsearch" group. > > > > To unsubscribe from this group and stop receiving emails from it, send an > > > > email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). > > > > To view this discussion on the web visit > > > > [https://groups.google.com/d/msgid/elasticsearch/70db39db-2317-46bc-a2a9-6011f454b46d%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/70db39db-2317-46bc-a2a9-6011f454b46d%40googlegroups.com) > > > > [https://groups.google.com/d/msgid/elasticsearch/70db39db-2317-46bc-a2a9-6011f454b46d%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/70db39db-2317-46bc-a2a9-6011f454b46d%40googlegroups.com?utm_medium=email&utm_source=footer) > > > > . > > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > > > -- > > You received this message because you are subscribed to the Google Groups > > "elasticsearch" group. > > To unsubscribe from this group and stop receiving emails from it, send an > > email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com) \. > > To view this discussion on the web visit > > [https://groups.google.com/d/msgid/elasticsearch/1ee83ee3-09a6-414f-87fe-faf9f97d06a6%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/1ee83ee3-09a6-414f-87fe-faf9f97d06a6%40googlegroups.com) > > [https://groups.google.com/d/msgid/elasticsearch/1ee83ee3-09a6-414f-87fe-faf9f97d06a6%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/1ee83ee3-09a6-414f-87fe-faf9f97d06a6%40googlegroups.com?utm_medium=email&utm_source=footer) > > . > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/8ee717c8-7937-40fa-8aa1-2d69982d8905%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/8ee717c8-7937-40fa-8aa1-2d69982d8905%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Artem\_Frolov](https://avatars.discourse-cdn.com/v4/letter/a/f04885/32.png) [@Artem\_Frolov](https://discuss.elastic.co/u/Artem_Frolov)\ **Post date:** [July 12, 2014, 11:14pm UTC](https://discuss.elastic.co/t/how-to-translate-and-or-where-clause-from-sql-query-to-elasticsearch-filter/18572/9 "2014-07-12T23:14:34Z")
Community, answer my question please! It's an actual problem still. ☹ пятница, 11 июля 2014 г., 17:15:04 UTC+3 пользователь Artem Frolov написал: > I've done what you want. > Mapping and populating data GIST: > [gist:3031c1ce8f95549ad86d · GitHub](https://gist.github.com/ArFeRR/3031c1ce8f95549ad86d) > > GIST with my search query: > [gist:f69ebe24ddc543b7bffd · GitHub](https://gist.github.com/ArFeRR/f69ebe24ddc543b7bffd) > > (it have to return one record with resolution:1920x1080 and weight: 2,9 > kg. It's the notebook with the name "Lenovo IdeaPad Z710A") > but it returns empty. > > Please help to achieve the behaviour what i need! > > пятница, 11 июля 2014 г., 11:48:40 UTC+3 пользователь David Pilato написал: > > > A full script would allow any user on the mailing list to recreate from > > scratch your issue without the need of building a script by ourselves which > > is really time consuming. > > > > So, basically a script should look like this: > > > > // Remove test data > > DELETE test > > > > // If needed, add your settings/mappings > > PUT test > > { > > "settings": {}, > > "mappings": {} > > } > > > > // Index some data > > PUT test/doc/1 > > { > > "foo":"bar" > > } > > > > PUT test/doc/x?refresh > > { > > "foo":"bar" > > } > > > > // Run the query > > GET test/doc/\_search > > { > > } > > > > With that, we can definitely help you I think. > > > > -- > > _David Pilato_ | _Technical Advocate_ | _[Elasticsearch.com](http://Elasticsearch.com)_ > > @dadoonet [https://twitter.com/dadoonet](https://twitter.com/dadoonet) | @elasticsearchfr > > [https://twitter.com/elasticsearchfr](https://twitter.com/elasticsearchfr) > > > > Le 11 juillet 2014 à 10:44:01, Artem Frolov ([kee...@gmail.com](mailto:kee...@gmail.com)) a écrit: > > > > Can you answer what's wrong with the gist, i've provided? I can't figure > > out... > > I provide you all the data I have, related to this issue. Look: > > > > 1. The elasticsearch index, containing the products and its options, > > which have to be filtered: > > [gist:de86b8b0a5f2bc7dfd86 · GitHub](https://gist.github.com/ArFeRR/de86b8b0a5f2bc7dfd86) > > 2. The JSON query for the filtration: > > [gist:e159ef1047122a617b88 · GitHub](https://gist.github.com/ArFeRR/e159ef1047122a617b88) > > 3)The [ELastica.io](http://ELastica.io) code to genereate the json above on PHP: > > [gist:cebb2bf54232069d817b · GitHub](https://gist.github.com/ArFeRR/cebb2bf54232069d817b#file-gistfile1-php) > > > > (I've changed the data to make it more real) > > > > All the json works fine, i've tested it! Can't figure out what's wrong > > and what I have to do to provide a "FULL working GIST" for you > > > > четверг, 10 июля 2014 г., 18:37:19 UTC+3 пользователь David Pilato > > написал: > > > > > Have a look at this page to see how you can build a full working GIST > > > which could help us to reproduce your use case. > > > > > > When your GIST will be updated, please update this thread so I can look > > > at it. > > > > > > ``` > > > -- > > > > > > ``` > > > > > > _David Pilato_ | _Technical Advocate_ | _[Elasticsearch.com](http://Elasticsearch.com)_ > > > @dadoonet [https://twitter.com/dadoonet](https://twitter.com/dadoonet) | @elasticsearchfr > > > [https://twitter.com/elasticsearchfr](https://twitter.com/elasticsearchfr) > > > > > > Le 10 juillet 2014 à 11:59:22, Artem Frolov ([kee...@gmail.com](mailto:kee...@gmail.com)) a écrit: > > > > > > the GIST: > > > [gist:630acb216b8d95168b73 · GitHub](https://gist.github.com/ArFeRR/630acb216b8d95168b73) > > > > > > четверг, 10 июля 2014 г., 12:51:49 UTC+3 пользователь Artem Frolov > > > написал: > > > > > > > here's my try to solve it: > > > > > > > > { > > > > "filtered":{ > > > > "filter":{ > > > > "nested":{ > > > > "path":"productsOptionValues", > > > > "filter":{ > > > > "and":[ > > > > { > > > > "or":[ > > > > { > > > > "and":[ > > > > { > > > > "term":{ > > > > > > > > "productsOptionValues.productOption":"weight" > > > > } > > > > }, > > > > { > > > > "term":{ > > > > "productsOptionValues.value": > > > > "500 kg" > > > > } > > > > } > > > > ] > > > > }, > > > > { > > > > "and":[ > > > > { > > > > "term":{ > > > > > > > > "productsOptionValues.productOption":"weight" > > > > } > > > > }, > > > > { > > > > "term":{ > > > > "productsOptionValues.value":"50kg" > > > > } > > > > } > > > > ] > > > > } > > > > ] > > > > }, > > > > { > > > > "or":[ > > > > { > > > > "and":[ > > > > { > > > > "term":{ > > > > > > > > "productsOptionValues.productOption":"magic" > > > > } > > > > }, > > > > { > > > > "term":{ > > > > "productsOptionValues.value":"no" > > > > } > > > > } > > > > ] > > > > } > > > > ] > > > > } > > > > ] > > > > } > > > > } > > > > } > > > > } > > > > } > > > > > > > > but it's an equivalent of: > > > > > > > > WHERE ((option = "weight" AND value = "50kg")OR (option = "weight" AND value = "500kg"))AND (option = "magic" AND value = "no") > > > > > > > > it's wrong tree... I need the and\or logic to be at one branch of the json tree. I don't know if it possible. Please help to translate the logic from the query where condition! > > > > > > > > четверг, 10 июля 2014 г., 12:23:05 UTC+3 пользователь David Pilato > > > > написал: > > > > > > > > > It could help if you could gist a full SENSE/curl script recreation > > > > > > > > > > Best > > > > > > > > > > -- > > > > > David 😉 > > > > > Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs > > > > > > > > > > Le 10 juil. 2014 à 11:15, Artem Frolov [kee...@gmail.com](mailto:kee...@gmail.com) a écrit : > > > > > > > > > > I have a WHERE clause in my SQL query, which have to be translated > > > > > into the elasticsearch bool filter. > > > > > > > > > > here's the where clause: > > > > > > > > > > WHERE (option = "weight" AND value = "50kg")OR (option = "weight" AND value = "500kg")AND (option = "magic" AND value = "no") > > > > > > > > > > I have written the AND filters for inner ANDs of query, but now I need > > > > > to put them to the bool filter. > > > > > > > > > > Tried to: > > > > > > > > > > $boolFilter = new \Elastica\Filter\Bool();$boolFilter-\>addShould($innerFilterAnd1);$boolFilter-\>addShould($innerFilterAnd2);$boolFilter-\>addMust($innerFilterAnd3); > > > > > > > > > > returns nothing. > > > > > > > > > > ## Please, help! > > > > > > > > > > You received this message because you are subscribed to the Google > > > > > Groups "elasticsearch" group. > > > > > To unsubscribe from this group and stop receiving emails from it, send > > > > > an email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). > > > > > To view this discussion on the web visit > > > > > [https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com) > > > > > [https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com?utm_medium=email&utm_source=footer) > > > > > . > > > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > > > > > > > > > -- > > > > > You received this message because you are subscribed to the Google > > > > > Groups "elasticsearch" group. > > > > > To unsubscribe from this group and stop receiving emails from it, send > > > > > an email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). > > > > > To view this discussion on the web visit > > > > > [https://groups.google.com/d/msgid/elasticsearch/70db39db-2317-46bc-a2a9-6011f454b46d%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/70db39db-2317-46bc-a2a9-6011f454b46d%40googlegroups.com) > > > > > [https://groups.google.com/d/msgid/elasticsearch/70db39db-2317-46bc-a2a9-6011f454b46d%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/70db39db-2317-46bc-a2a9-6011f454b46d%40googlegroups.com?utm_medium=email&utm_source=footer) > > > > > . > > > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > > > > > -- > > > You received this message because you are subscribed to the Google Groups > > > "elasticsearch" group. > > > To unsubscribe from this group and stop receiving emails from it, send an > > > email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). > > > To view this discussion on the web visit > > > [https://groups.google.com/d/msgid/elasticsearch/1ee83ee3-09a6-414f-87fe-faf9f97d06a6%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/1ee83ee3-09a6-414f-87fe-faf9f97d06a6%40googlegroups.com) > > > [https://groups.google.com/d/msgid/elasticsearch/1ee83ee3-09a6-414f-87fe-faf9f97d06a6%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/1ee83ee3-09a6-414f-87fe-faf9f97d06a6%40googlegroups.com?utm_medium=email&utm_source=footer) > > > . > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/76b2845e-f4d4-4174-b641-bffd5a83ddbf%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/76b2845e-f4d4-4174-b641-bffd5a83ddbf%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Yarek\_T](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/yarek_t/32/1410_2.png) [@Yarek\_T](https://discuss.elastic.co/u/Yarek_T)\ **Post date:** [July 14, 2014, 10:03am UTC](https://discuss.elastic.co/t/how-to-translate-and-or-where-clause-from-sql-query-to-elasticsearch-filter/18572/10 "2014-07-14T10:03:23Z")
Always build up your logic bit by bit. Don't just bang it all in at once. Once you understand how individual components evaluate you will be able to build a working complex query. [https://gist.github.com/yarekt/4bd8e0bccc8ca694c855](https://gist.github.com/yarekt/4bd8e0bccc8ca694c855) On Sunday, 13 July 2014 00:14:34 UTC+1, Artem Frolov wrote: > Community, answer my question please! It's an actual problem still. ☹ > > пятница, 11 июля 2014 г., 17:15:04 UTC+3 пользователь Artem Frolov написал: > > > I've done what you want. > > Mapping and populating data GIST: > > [gist:3031c1ce8f95549ad86d · GitHub](https://gist.github.com/ArFeRR/3031c1ce8f95549ad86d) > > > > GIST with my search query: > > [gist:f69ebe24ddc543b7bffd · GitHub](https://gist.github.com/ArFeRR/f69ebe24ddc543b7bffd) > > > > (it have to return one record with resolution:1920x1080 and weight: 2,9 > > kg. It's the notebook with the name "Lenovo IdeaPad Z710A") > > but it returns empty. > > > > Please help to achieve the behaviour what i need! > > > > пятница, 11 июля 2014 г., 11:48:40 UTC+3 пользователь David Pilato > > написал: > > > > > A full script would allow any user on the mailing list to recreate from > > > scratch your issue without the need of building a script by ourselves which > > > is really time consuming. > > > > > > So, basically a script should look like this: > > > > > > // Remove test data > > > DELETE test > > > > > > // If needed, add your settings/mappings > > > PUT test > > > { > > > "settings": {}, > > > "mappings": {} > > > } > > > > > > // Index some data > > > PUT test/doc/1 > > > { > > > "foo":"bar" > > > } > > > > > > PUT test/doc/x?refresh > > > { > > > "foo":"bar" > > > } > > > > > > // Run the query > > > GET test/doc/\_search > > > { > > > } > > > > > > With that, we can definitely help you I think. > > > > > > -- > > > _David Pilato_ | _Technical Advocate_ | _[Elasticsearch.com](http://Elasticsearch.com)_ > > > @dadoonet [https://twitter.com/dadoonet](https://twitter.com/dadoonet) | @elasticsearchfr > > > [https://twitter.com/elasticsearchfr](https://twitter.com/elasticsearchfr) > > > > > > Le 11 juillet 2014 à 10:44:01, Artem Frolov ([kee...@gmail.com](mailto:kee...@gmail.com)) a écrit: > > > > > > Can you answer what's wrong with the gist, i've provided? I can't > > > figure out... > > > I provide you all the data I have, related to this issue. Look: > > > > > > 1. The elasticsearch index, containing the products and its options, > > > which have to be filtered: > > > [gist:de86b8b0a5f2bc7dfd86 · GitHub](https://gist.github.com/ArFeRR/de86b8b0a5f2bc7dfd86) > > > 2. The JSON query for the filtration: > > > [gist:e159ef1047122a617b88 · GitHub](https://gist.github.com/ArFeRR/e159ef1047122a617b88) > > > 3)The [ELastica.io](http://ELastica.io) code to genereate the json above on PHP: > > > [gist:cebb2bf54232069d817b · GitHub](https://gist.github.com/ArFeRR/cebb2bf54232069d817b#file-gistfile1-php) > > > > > > (I've changed the data to make it more real) > > > > > > All the json works fine, i've tested it! Can't figure out what's wrong > > > and what I have to do to provide a "FULL working GIST" for you > > > > > > четверг, 10 июля 2014 г., 18:37:19 UTC+3 пользователь David Pilato > > > написал: > > > > > > > Have a look at this page to see how you can build a full working GIST > > > > which could help us to reproduce your use case. > > > > > > > > When your GIST will be updated, please update this thread so I can > > > > look at it. > > > > > > > > ``` > > > > -- > > > > > > > > ``` > > > > > > > > _David Pilato_ | _Technical Advocate_ | _[Elasticsearch.com](http://Elasticsearch.com)_ > > > > @dadoonet [https://twitter.com/dadoonet](https://twitter.com/dadoonet) | @elasticsearchfr > > > > [https://twitter.com/elasticsearchfr](https://twitter.com/elasticsearchfr) > > > > > > > > Le 10 juillet 2014 à 11:59:22, Artem Frolov ([kee...@gmail.com](mailto:kee...@gmail.com)) a écrit: > > > > > > > > the GIST: > > > > [gist:630acb216b8d95168b73 · GitHub](https://gist.github.com/ArFeRR/630acb216b8d95168b73) > > > > > > > > четверг, 10 июля 2014 г., 12:51:49 UTC+3 пользователь Artem Frolov > > > > написал: > > > > > > > > > here's my try to solve it: > > > > > > > > > > { > > > > > "filtered":{ > > > > > "filter":{ > > > > > "nested":{ > > > > > "path":"productsOptionValues", > > > > > "filter":{ > > > > > "and":[ > > > > > { > > > > > "or":[ > > > > > { > > > > > "and":[ > > > > > { > > > > > "term":{ > > > > > > > > > > "productsOptionValues.productOption":"weight" > > > > > } > > > > > }, > > > > > { > > > > > "term":{ > > > > > "productsOptionValues.value": > > > > > "500 kg" > > > > > } > > > > > } > > > > > ] > > > > > }, > > > > > { > > > > > "and":[ > > > > > { > > > > > "term":{ > > > > > > > > > > "productsOptionValues.productOption":"weight" > > > > > } > > > > > }, > > > > > { > > > > > "term":{ > > > > > "productsOptionValues.value": > > > > > "50kg" > > > > > } > > > > > } > > > > > ] > > > > > } > > > > > ] > > > > > }, > > > > > { > > > > > "or":[ > > > > > { > > > > > "and":[ > > > > > { > > > > > "term":{ > > > > > > > > > > "productsOptionValues.productOption":"magic" > > > > > } > > > > > }, > > > > > { > > > > > "term":{ > > > > > "productsOptionValues.value":"no" > > > > > } > > > > > } > > > > > ] > > > > > } > > > > > ] > > > > > } > > > > > ] > > > > > } > > > > > } > > > > > } > > > > > } > > > > > } > > > > > > > > > > but it's an equivalent of: > > > > > > > > > > WHERE ((option = "weight" AND value = "50kg")OR (option = "weight" AND value = "500kg"))AND (option = "magic" AND value = "no") > > > > > > > > > > it's wrong tree... I need the and\or logic to be at one branch of the json tree. I don't know if it possible. Please help to translate the logic from the query where condition! > > > > > > > > > > четверг, 10 июля 2014 г., 12:23:05 UTC+3 пользователь David Pilato > > > > > написал: > > > > > > > > > > > It could help if you could gist a full SENSE/curl script recreation > > > > > > > > > > > > Best > > > > > > > > > > > > -- > > > > > > David 😉 > > > > > > Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs > > > > > > > > > > > > Le 10 juil. 2014 à 11:15, Artem Frolov [kee...@gmail.com](mailto:kee...@gmail.com) a écrit : > > > > > > > > > > > > I have a WHERE clause in my SQL query, which have to be translated > > > > > > into the elasticsearch bool filter. > > > > > > > > > > > > here's the where clause: > > > > > > > > > > > > WHERE (option = "weight" AND value = "50kg")OR (option = "weight" AND value = "500kg")AND (option = "magic" AND value = "no") > > > > > > > > > > > > I have written the AND filters for inner ANDs of query, but now I > > > > > > need to put them to the bool filter. > > > > > > > > > > > > Tried to: > > > > > > > > > > > > $boolFilter = new \Elastica\Filter\Bool();$boolFilter-\>addShould($innerFilterAnd1);$boolFilter-\>addShould($innerFilterAnd2);$boolFilter-\>addMust($innerFilterAnd3); > > > > > > > > > > > > returns nothing. > > > > > > > > > > > > ## Please, help! > > > > > > > > > > > > You received this message because you are subscribed to the Google > > > > > > Groups "elasticsearch" group. > > > > > > To unsubscribe from this group and stop receiving emails from it, > > > > > > send an email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). > > > > > > To view this discussion on the web visit > > > > > > [https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com) > > > > > > [https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com?utm_medium=email&utm_source=footer) > > > > > > . > > > > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > > > > > > > > > > > -- > > > > > > You received this message because you are subscribed to the Google > > > > > > Groups "elasticsearch" group. > > > > > > To unsubscribe from this group and stop receiving emails from it, send > > > > > > an email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). > > > > > > To view this discussion on the web visit > > > > > > [https://groups.google.com/d/msgid/elasticsearch/70db39db-2317-46bc-a2a9-6011f454b46d%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/70db39db-2317-46bc-a2a9-6011f454b46d%40googlegroups.com) > > > > > > [https://groups.google.com/d/msgid/elasticsearch/70db39db-2317-46bc-a2a9-6011f454b46d%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/70db39db-2317-46bc-a2a9-6011f454b46d%40googlegroups.com?utm_medium=email&utm_source=footer) > > > > > > . > > > > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > > > > > > > -- > > > > You received this message because you are subscribed to the Google > > > > Groups "elasticsearch" group. > > > > To unsubscribe from this group and stop receiving emails from it, send > > > > an email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). > > > > To view this discussion on the web visit > > > > [https://groups.google.com/d/msgid/elasticsearch/1ee83ee3-09a6-414f-87fe-faf9f97d06a6%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/1ee83ee3-09a6-414f-87fe-faf9f97d06a6%40googlegroups.com) > > > > [https://groups.google.com/d/msgid/elasticsearch/1ee83ee3-09a6-414f-87fe-faf9f97d06a6%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/1ee83ee3-09a6-414f-87fe-faf9f97d06a6%40googlegroups.com?utm_medium=email&utm_source=footer) > > > > . > > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/78bbc2e6-5d8b-4ffc-b35e-b8e2315d734e%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/78bbc2e6-5d8b-4ffc-b35e-b8e2315d734e%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Artem\_Frolov](https://avatars.discourse-cdn.com/v4/letter/a/f04885/32.png) [@Artem\_Frolov](https://discuss.elastic.co/u/Artem_Frolov)\ **Post date:** [July 14, 2014, 2:56pm UTC](https://discuss.elastic.co/t/how-to-translate-and-or-where-clause-from-sql-query-to-elasticsearch-filter/18572/11 "2014-07-14T14:56:06Z")
I've just figured out the problem. It's no need in my case to remap the data. It's only needed to change the type:nested to type:object in my mapping of productOptionValues property. понедельник, 14 июля 2014 г., 13:03:23 UTC+3 пользователь Yarek T написал: > Always build up your logic bit by bit. Don't just bang it all in at once. > Once you understand how individual components evaluate you will be able to > build a working complex query. > > [https://gist.github.com/yarekt/4bd8e0bccc8ca694c855](https://gist.github.com/yarekt/4bd8e0bccc8ca694c855) > > On Sunday, 13 July 2014 00:14:34 UTC+1, Artem Frolov wrote: > > Community, answer my question please! It's an actual problem still. ☹ > > пятница, 11 июля 2014 г., 17:15:04 UTC+3 пользователь Artem Frolov написал: > > I've done what you want. > Mapping and populating data GIST: > [gist:3031c1ce8f95549ad86d · GitHub](https://gist.github.com/ArFeRR/3031c1ce8f95549ad86d) > > GIST with my search query: > [gist:f69ebe24ddc543b7bffd · GitHub](https://gist.github.com/ArFeRR/f69ebe24ddc543b7bffd) > > (it have to return one record with resolution:1920x1080 and weight: 2,9 > kg. It's the notebook with the name "Lenovo IdeaPad Z710A") > but it returns empty. > > Please help to achieve the behaviour what i need! > > пятница, 11 июля 2014 г., 11:48:40 UTC+3 пользователь David Pilato написал: > > A full script would allow any user on the mailing list to recreate from > scratch your issue without the need of building a script by ourselves which > is really time consuming. > > So, basically a script should look like this: > > // Remove test data > DELETE test > > // If needed, add your settings/mappings > PUT test > { > "settings": {}, > "mappings": {} > } > > // Index some data > PUT test/doc/1 > { > "foo":"bar" > } > > PUT test/doc/x?refresh > { > "foo":"bar" > } > > // Run the query > GET test/doc/\_search > { > } > > With that, we can definitely help you I think. > > -- > _David Pilato_ | _Technical Advocate_ | _[Elasticsearch.com](http://Elasticsearch.com)_ > @dadoonet [https://twitter.com/dadoonet](https://twitter.com/dadoonet) | @elasticsearchfr > [https://twitter.com/elasticsearchfr](https://twitter.com/elasticsearchfr) > > Le 11 juillet 2014 à 10:44:01, Artem Frolov ([kee...@gmail.com](mailto:kee...@gmail.com)) a écrit: > > Can you answer what's wrong with the gist, i've provided? I can't figure > out... > I provide you all the data I have, related to this issue. Look: > > 1. The elasticsearch index, containing the products and its options, which > have to be filtered: [gist:de86b8b0a5f2bc7dfd86 · GitHub](https://gist.github.com/ArFeRR/de86b8b0a5f2bc7dfd86) > 2. The JSON query for the filtration: > [gist:e159ef1047122a617b88 · GitHub](https://gist.github.com/ArFeRR/e159ef1047122a617b88) > 3)The [ELastica.io](http://ELastica.io) code to genereate the json above on PHP: > [gist:cebb2bf54232069d817b · GitHub](https://gist.github.com/ArFeRR/cebb2bf54232069d817b#file-gistfile1-php) > > (I've changed the data to make it more real) > > All the json works fine, i've tested it! Can't figure out what's wrong and > what I have to do to provide a "FULL working GIST" for you > > четверг, 10 июля 2014 г., 18:37:19 UTC+3 пользователь David Pilato > написал: > > Have a look at this page to see how you can build a full working GIST > which could help us to reproduce your use case. > > When your GIST will be updated, please update this thread so I can look > at it. > > ``` > -- > > ``` > > _David Pilato_ | _Technical Advocate_ | _[Elasticsearch.com](http://Elasticsearch.com)_ > @dadoonet [https://twitter.com/dadoonet](https://twitter.com/dadoonet) | @elasticsearchfr > [https://twitter.com/elasticsearchfr](https://twitter.com/elasticsearchfr) > > Le 10 juillet 2014 à 11:59:22, Artem Frolov ([kee...@gmail.com](mailto:kee...@gmail.com)) a écrit: > > the GIST: > [gist:630acb216b8d95168b73 · GitHub](https://gist.github.com/ArFeRR/630acb216b8d95168b73) > > четверг, 10 июля 2014 г., 12:51:49 UTC+3 пользователь Artem Frolov написал: > > here's my try to solve it: > > { > "filtered":{ > "filter":{ > "nested":{ > "path":"productsOptionValues", > "filter":{ > "and":[ > { > "or":[ > { > "and":[ > { > "term":{ > "productsOptionValues.productOption": > "weight" > } > }, > { > "term":{ > "productsOptionValues.value":"500 kg" > } > } > ] > }, > { > "and":[ > { > "term":{ > "productsOptionValues.productOption": > "weight" > } > }, > { > "term":{ > "productsOptionValues.value":"50kg" > } > } > ] > } > ] > }, > { > "or":[ > { > "and":[ > { > "term":{ > "productsOptionValues.productOption": > "magic" > } > }, > { > "term":{ > "productsOptionValues.value":"no" > } > } > ] > } > ] > } > ] > } > } > } > } > } > > but it's an equivalent of: > > WHERE ((option = "weight" AND value = "50kg")OR (option = "weight" AND value = "500kg"))AND (option = "magic" AND value = "no") > > it's wrong tree... I need the and\or logic to be at one branch of the json tree. I don't know if it possible. Please help to translate the logic from the query where condition! > > четверг, 10 июля 2014 г., 12:23:05 UTC+3 пользователь David Pilato > написал: > > It could help if you could gist a full SENSE/curl script recreation > > Best > > -- > David 😉 > Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs > > Le 10 juil. 2014 à 11:15, Artem Frolov [kee...@gmail.com](mailto:kee...@gmail.com) a écrit : > > I have a WHERE clause in my SQL query, which have to be translated into > the elasticsearch bool filter. > > here's the where clause: > > WHERE (option = "weight" AND value = "50kg")OR (option = "weight" AND value = "500kg")AND (option = "magic" AND value = "no") > > I have written the AND filters for inner ANDs of query, but now I need to > put them to the bool filter. > > Tried to: > > $boolFilter = new \Elastica\Filter\Bool();$boolFilter-\>addShould($innerFilterAnd1);$boolFilter-\>addShould($innerFilterAnd2);$boolFilter-\>addMust($innerFilterAnd3); > > returns nothing. > > ## Please, help! > > You received this message because you are subscribed to the Google Groups > "elasticsearch" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). > To view this discussion on the web visit > [https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com) > [https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/5291b465-50d1-42da-a8a0-45ee154c1838%40googlegroups.com?utm_medium=email&utm_source=footer) > . > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > -- > You received this message because you are subscribed to the Google Groups > "elasticsearch" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). > To view this discussion on the web visit > [https://groups.google.com/d/msgid/elasticsearch/70db39db-2317-46bc-a2a9-6011f454b46d%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/70db39db-2317-46bc-a2a9-6011f454b46d%40googlegroups.com) > [https://groups.google.com/d/msgid/elasticsearch/70db39db-2317-46bc-a2a9-6011f454b46d%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/70db39db-2317-46bc-a2a9-6011f454b46d%40googlegroups.com?utm_medium=email&utm_source=footer) > . > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > -- > You received this message because you are subscribed to the Google Groups > "elasticsearch" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). > To view this discussion on the web visit > [https://groups.google.com/d/msgid/elasticsearch/1ee83ee3-09a6-414f-87fe-faf9f97d06a6%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/1ee83ee3-09a6-414f-87fe-faf9f97d06a6%40googlegroups.com) > [https://groups.google.com/d/msgid/elasticsearch/1ee83ee3-09a6-414f-87fe-faf9f97d06a6%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/1ee83ee3-09a6-414f-87fe-faf9f97d06a6%40googlegroups.com?utm_medium=email&utm_source=footer) > . > For more options, visit \ target="\_blank" onmousedown="this.href=' > [https://groups.google.com/d/optout](https://groups.google.com/d/optout)';return true;" onclick=" > > ... -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/9c735e1d-6c38-4d93-a3e9-19c884db5ef8%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/9c735e1d-6c38-4d93-a3e9-19c884db5ef8%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 1:16am UTC](https://discuss.elastic.co/t/how-to-translate-and-or-where-clause-from-sql-query-to-elasticsearch-filter/18572/12 "2017-07-06T01:16:03Z")