Is there any NEST tool to convert sql query to DSL


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

What do you mean?


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": [
  "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

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

More about Bool Queries here:

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 which is handy to help you generate a Query DSL.

1 Like

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