# Is there any NEST tool to convert sql query to DSL

**URL:** <https://discuss.elastic.co/t/is-there-any-nest-tool-to-convert-sql-query-to-dsl/180297>\
**Category:** Elasticsearch\
**Created:** [May 9, 2019, 7:04am UTC](https://discuss.elastic.co/t/is-there-any-nest-tool-to-convert-sql-query-to-dsl/180297 "2019-05-09T07:04:35Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![emir](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/emir/32/45868_2.png) [@emir](https://discuss.elastic.co/u/emir)\
**Post date:** [May 9, 2019, 7:04am UTC](https://discuss.elastic.co/t/is-there-any-nest-tool-to-convert-sql-query-to-dsl/180297/1 "2019-05-09T07:04:35Z")

</div>

Hi,

I'm quite new for document databases and DSL queries.  
I would like to achieve to create a dynamic filter queries with AND and OR conditions.  
I've used the NEST library to achieve that but it does not work as well when added OR filter.

What do you offer to complete my task with NEST

Thanks in advance

---

<div class="post-metadata">

**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:** [May 9, 2019, 7:20am UTC](https://discuss.elastic.co/t/is-there-any-nest-tool-to-convert-sql-query-to-dsl/180297/2 "2019-05-09T07:20:01Z")

</div>

> [@emir](#):
>
> but it does not work as well when added OR filter.

What do you mean?

---

<div class="post-metadata">

**Author:** ![emir](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/emir/32/45868_2.png) [@emir](https://discuss.elastic.co/u/emir)\
**Post date:** [May 9, 2019, 7:50am UTC](https://discuss.elastic.co/t/is-there-any-nest-tool-to-convert-sql-query-to-dsl/180297/3 "2019-05-09T07:50:31Z")

</div>

Hi,

I will try to explain what is my challenge.

My data like below;

```
{
  "bulkStock": 5,
  "calculatedPickStock": 2,
  "calculatedStock": 7,
  "completenessPercentage": 53.84615384615385,
  "countStock": 10,
  "createdDateTime": "2019-05-09T06:16:33.7233333",
  "customerId": 1003,
  "entityId": 43123,
  "entityType": 0,
  "familyId": 4,
  "hasAssembly": false,
  "id": 1231231,
  "pickStock": 5,
  "selectedListProducts": [],
  "selectedStoreProducts": [
    86
  ],
  "selectedSuppliers": [],
  "valueList-attr_1067": "SKU_14548",
  "valueList-attr_1068-l_131": "Testbeispiel18",
  "valueList-attr_1068-l_59": "TestExample18",
  "valueList-attr_1068-l_86": "TestExample18",
  "valueList-attr_1069": "262882325",
  "valueList-attr_1070": null,
  "valueList-attr_1071": null,
  "valueList-attr_1073": null,
  "valueList-attr_1074": [],
  "valueList-attr_1076": null,
  "valueList-attr_1084": null,
  "valueList-attr_2169": null,
  "variantList": [
    {
      "completenessPercentage": 46.15384615384615,
      "createdDateTime": "2019-05-09T06:16:33Z",
      "customerId": 1003,
      "entityId": 45312,
      "entityType": 0,
      "familyId": 4,
      "hasAssembly": false,
      "id": 1234421,
      "selectedListProducts": [],
      "selectedStoreProducts": [],
      "selectedSuppliers": [],
      "valueList-attr_1067": "SKU_123141123",
      "valueList-attr_1068-l_131": "Testbeispiel18 variant",
      "valueList-attr_1068-l_59": "TestExample18 variant",
      "valueList-attr_1068-l_86": "TestExample18 variant",
      "valueList-attr_1074": [],
      "variantMasterId": 1231231
    }
  ],
  "variantMasterId": null
}

```

I want to filter my results by name attributes.  
Name attribute Id is 1068 and it has many variants for language specific and also same attributes in "variantList" array.  
_Ps: in this example "valueList-attr\_1068-l\_ and "variantList.valueList-attr\_1068-I\_\*"_\*

The query will be generated dynamically according to the user inputs.

For example countStock\>10 AND (Name LIKE 'Test%' OR Name LIKE 'Purple%)

Query in SQL, should be like  
WHERE countStock\>10 AND  
(valueList-attr\_1068-l\_131 LIKE 'Test%'  
OR valueList-attr\_1068-l\_131 LIKE 'Purple%'  
OR valueList-attr\_1068-l\_86 LIKE 'Test%'  
OR valueList-attr\_1068-l\_86 LIKE 'Purple%' ..... and so on)

I could not found how to build in NEST or DSL query.  
I hope I could express myself this time

Best Regards

---

<div class="post-metadata">

**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:** [May 9, 2019, 8:39am UTC](https://discuss.elastic.co/t/is-there-any-nest-tool-to-convert-sql-query-to-dsl/180297/4 "2019-05-09T08:39:35Z")

</div>

You can use a `bool` query to simulate the `OR` part, with a `should` array.

More about Bool Queries here: [https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-bool-query.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-bool-query.html)

To do the `AND` part, you can also use a bool query, with a `must` array which contains the previous `bool` part and a `filter` with a `range` query on `countStock` field.

You can also have a look at the SQL feature. It has a [translate API](https://www.elastic.co/guide/en/elasticsearch/reference/7.0/sql-translate.html) which is handy to help you generate a Query DSL.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [June 6, 2019, 8:52am UTC](https://discuss.elastic.co/t/is-there-any-nest-tool-to-convert-sql-query-to-dsl/180297/5 "2019-06-06T08:52:31Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.