CSV export doesn't export all fields

[ppisljar]

Kibana version:
tested from 7.4 to master

Describe the bug:
When exporting saved search as CSV some fields are empty (it seems non primary date fields)

Steps to reproduce:

1. add ecommerce sample data, go to discover
2. add order_date and products.created_on fields and save a search
3. create dashboard, add this search and save the dashboard
4. click gear on search -> export CSV ... check downloaded csv file, products.created_on field will be missing values.

[elasticmachine]

Pinging @elastic/kibana-stack-services (Team:Stack Services)

[tsullivan]

Hi, @ppisljar Reporting team needs to treat this as an integration issue of Discover app. There needs to be a common API for fetching and formatting the data which is owned by and used by Discover, and shared by Discover to create a maintainable Reporting solution.

In the past, Reporting has been responsible for gathering and formatting the field data and getting it in parity with the Discover UI, but that arrangement is not maintainable for the future.

The Reporting team is going ask that the App Arch team find a way to make Discover more interoperable and provide a function to abstract the querying, scripting, and formatting of the user's saved search.

Reporting team will work with App Arch to integrate this logic into an API that can be called from automation.

Is there someone in the App Arch team that can help here?

cc @stacey-gammon @lukasolson @wylieconlon

[elasticmachine]

Pinging @elastic/kibana-app-arch (Team:AppArch)

[ppisljar]

i would love to help, but i will need some more background.

[tsullivan]

Hi, my first thought is that there is a gap of functionality with Discover for querying/formatting, and the functionality that CSV Download has. The logic itself is different because CSV Download is trying to emulate logic that Discover has only on the browser side (hopefully will change).

I took a look at what we are getting back from esQuery.buildEsQuery in x-pack/legacy/plugins/reporting/export_types/csv_from_savedobject/server/lib/generate_csv_search.ts. The search and outcoming data looks ok.

Going from there, it looks like this issue is a bug in Reporting's flattenHit function which gets called in generateCsv

• https://github.com/elastic/kibana/blob/9769be3/x-pack/legacy/plugins/reporting/export_types/csv_from_savedobject/server/lib/generate_csv_search.ts
• https://github.com/elastic/kibana/blob/7ac8e4d/x-pack/legacy/plugins/reporting/export_types/csv/server/lib/flatten_hit.js

The flatten hit code hasn't been touched in a long time, and there may be a bug where it's trying to put together the multi-value object.

Specifically, here's a multi-value object getting lost, in an example of the source JSON:

          "products" : [
            {
              "created_on" : "2016-12-14T05:06:43+00:00"
            },
            {
              "created_on" : "2016-12-14T05:06:43+00:00"
            }
          ],

AppArch team, sorry for the unnecessary ping. It looks like this is a bug definitely on the Reporting side. It would be great to consolidate the flatten hit / field mapping work that Reporting does for CSV export, but that's long term for sure.

I was concerned the issue was stemming from how we use esQuery.buildEsQuery, but I see that code in Reporting has been updated recently - I must have missed that change. Thanks!