[BUG] DQL/KQL queries gives results in Discover but not in csv reports

[davidcui1225]

Describe the bug
@zhongnansu
in the Discover query bar using DQL, if it's not strictly using the uppercase, or double quotes, even tho it gives results on Discover, but reports will be empty.

Example

both
geoip.city_name:"New York" AND geoip.continent_name: "North America"
and
geoip.city_name:New York and geoip.continent_name: North America
give same results on Discover, but the latter generate empty csv report.

[zhongnansu]

Root cause:

When Reporting csv logic handles filters in the form of DQL/KQL queries, it is using esb.queryStringQuery() to build query, which takes in a DSL query string, but the input from Dashboards is actually DQL(Dashboard query language).

Ref:

• query string sytax
• DQL/KQL

https://github.com/opensearch-project/dashboards-reports/blob/56008de1dd447b3b4967e9abdea692e1b4036119/dashboards-reports/server/routes/utils/dataReportHelpers.ts#L143-L149

Solution: TODO

[zhongnansu]

Currenly there's a OSD utility method buildOpensearchQuery that can convert a DQL query to a DSL query. But since reporting csv is using elastic-build(esb). There's not cleat path to add a complete DSL qeury json to the elastic-builder .

I tied an implenetation like this, and the ??? part is where I can't figure out the DSL query => esb.Query conversion

import {
  buildOpenSearchQuery,
  Filter,
  Query,
} from '../../../../../src/plugins/data/common';

 //search part
  let dqlQuery: Query = JSON.parse(filters).query;
  let dqlFilter: Filter = JSON.parse(filters).filter;

  const opensearchQuery = buildOpenSearchQuery(undefined, dqlQuery, dqlFilter);
  if (opensearchQuery) {
    requestBody.must(???));
  }

I am thinking in the future is there a chance of not using elastic-builder? I don't think that's maintained by Opensearch, and was not created originally by Elastic either

@joshuali925 @davidcui1225 @anirudha any thought?

[joshuali925]

esb doesn't support constructing from JSON (author provided some workarounds in sudo-suhas/elastic-builder issue 28). I think we can only either parse opensearchQuery to insert into requestBody properly, or merge it with requestBody.toJSON(), or look into workarounds in that issue.

FYI trace-analytics' filter DSL is directly built in JSON. It doesn't support everything in discover filter and has many TA specific stuff, but reporting could do something similar. Not sure if it's a better approach though, need to check how Dashboards converts filters to DSL