Is there any NEST tool to convert sql query to DSL

[emir (Emir Kuzeyli)]

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

[dadoonet (David Pilato)]

@emir

but it does not work as well when added OR filter.

What do you mean?

[emir (Emir Kuzeyli)]

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

[dadoonet (David Pilato)]

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

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.