API: Columns shift when EXPORT_BATCH_SIZE is surpassed on exporting CSV

[joselcvarela]

Describe the Bug

When exporting items from a collection to File Library we are seeing some data in wrong columns.
We see this happening when in one batch the relationship is null for some records but not all and in another batch the relationship is never null.

To explain a bit better, here's screen recording:

Screen.Recording.2024-04-23.at.18.48.52.mov

The issue seems to come from json2csv and the usage of flatten.
As mentioned earlier, in first batch this is how data is passed to CSVParser:

┌─────────┬─────────┬──────────────┬────┬──────┬────────────────────────────────────────┬────────────────────────────┬────────────────────────────┬────────┬──────────┬──────────┬──────────────┐
│ (index) │ status  │    title     │ id │ sort │              user_created              │        date_created        │        date_updated        │  note  │  author  │ category │ user_updated │
├─────────┼─────────┼──────────────┼────┼──────┼────────────────────────────────────────┼────────────────────────────┼────────────────────────────┼────────┼──────────┼──────────┼──────────────┤
│    0    │ 'draft' │ 'Article 1'  │ 1  │ null │ '29556c73-bc9b-4233-b23c-f95dadff7635' │ '2024-04-23T17:29:36.305Z' │ '2024-04-23T17:35:04.352Z' │  null  │ [Object] │ [Object] │   [Object]   │
│    1    │ 'draft' │ 'Article 2'  │ 2  │ null │ '29556c73-bc9b-4233-b23c-f95dadff7635' │ '2024-04-23T17:30:03.565Z' │ '2024-04-23T17:36:38.959Z' │ 'Note' │   null   │ [Object] │   [Object]   │
│    2    │ 'draft' │ 'Article 3'  │ 3  │ null │ '29556c73-bc9b-4233-b23c-f95dadff7635' │ '2024-04-23T17:30:07.264Z' │ '2024-04-23T17:30:28.475Z' │  null  │   null   │ [Object] │   [Object]   │
│    3    │ 'draft' │ 'Article 4'  │ 4  │ null │ '29556c73-bc9b-4233-b23c-f95dadff7635' │ '2024-04-23T17:30:12.224Z' │ '2024-04-23T17:30:35.684Z' │  null  │   null   │   null   │   [Object]   │
│    4    │ 'draft' │ 'Article 5'  │ 5  │ null │ '29556c73-bc9b-4233-b23c-f95dadff7635' │ '2024-04-23T17:30:15.328Z' │ '2024-04-23T17:30:43.300Z' │  null  │   null   │   null   │   [Object]   │
│    5    │ 'draft' │ 'Article 6'  │ 6  │ null │ '29556c73-bc9b-4233-b23c-f95dadff7635' │ '2024-04-23T17:30:48.738Z' │ '2024-04-23T17:30:43.300Z' │  null  │   null   │   null   │   [Object]   │
│    6    │ 'draft' │ 'Article 7'  │ 7  │ null │ '29556c73-bc9b-4233-b23c-f95dadff7635' │ '2024-04-23T17:30:52.368Z' │ '2024-04-23T17:30:43.300Z' │  null  │   null   │   null   │   [Object]   │
│    7    │ 'draft' │ 'Article 8'  │ 8  │ null │ '29556c73-bc9b-4233-b23c-f95dadff7635' │ '2024-04-23T17:30:55.607Z' │ '2024-04-23T17:30:43.300Z' │  null  │   null   │   null   │   [Object]   │
│    8    │ 'draft' │ 'Article 9'  │ 9  │ null │ '29556c73-bc9b-4233-b23c-f95dadff7635' │ '2024-04-23T17:30:59.239Z' │ '2024-04-23T17:30:43.300Z' │  null  │   null   │   null   │   [Object]   │
│    9    │ 'draft' │ 'Article 10' │ 10 │ null │ '29556c73-bc9b-4233-b23c-f95dadff7635' │ '2024-04-23T17:31:03.392Z' │ '2024-04-23T17:30:43.300Z' │  null  │   null   │   null   │   [Object]   │
└─────────┴─────────┴──────────────┴────┴──────┴────────────────────────────────────────┴────────────────────────────┴────────────────────────────┴────────┴──────────┴──────────┴──────────────┘

And on second batch:

┌─────────┬─────────┬──────────────┬────┬──────┬────────────────────────────────────────┬────────────────────────────┬────────────────────────────┬──────┬────────┬──────────┬──────────────┐
│ (index) │ status  │    title     │ id │ sort │              user_created              │        date_created        │        date_updated        │ note │ author │ category │ user_updated │
├─────────┼─────────┼──────────────┼────┼──────┼────────────────────────────────────────┼────────────────────────────┼────────────────────────────┼──────┼────────┼──────────┼──────────────┤
│    0    │ 'draft' │ 'Article 11' │ 11 │ null │ '29556c73-bc9b-4233-b23c-f95dadff7635' │ '2024-04-23T17:31:13.897Z' │ '2024-04-23T17:30:43.300Z' │ null │  null  │ [Object] │   [Object]   │
│    1    │ 'draft' │ 'Article 12' │ 12 │ null │ '29556c73-bc9b-4233-b23c-f95dadff7635' │ '2024-04-23T17:31:18.173Z' │ '2024-04-23T17:30:43.300Z' │ null │  null  │ [Object] │   [Object]   │
│    2    │ 'draft' │ 'Article 13' │ 13 │ null │ '29556c73-bc9b-4233-b23c-f95dadff7635' │ '2024-04-23T17:31:21.186Z' │ '2024-04-23T17:30:43.300Z' │ null │  null  │ [Object] │   [Object]   │
│    3    │ 'draft' │ 'Article 14' │ 14 │ null │ '29556c73-bc9b-4233-b23c-f95dadff7635' │ '2024-04-23T17:31:24.315Z' │ '2024-04-23T17:30:43.300Z' │ null │  null  │ [Object] │   [Object]   │
└─────────┴─────────┴──────────────┴────┴──────┴────────────────────────────────────────┴────────────────────────────┴────────────────────────────┴──────┴────────┴──────────┴──────────────┘

As can be seen, on second batch, there's no record with author, so the flatten from json2csv is not able to expand.

To Reproduce

The configuration EXPORT_BATCH_SIZE should be set to 10

Here's the database used on the video:
export-column-shift-issue.sql.zip
Admin : admin@example.org
Password: password

Here's the request (exported as cURL from Directus App):

curl 'http://localhost:8055/utils/export/articles' \
  -H 'Accept: application/json, text/plain, */*' \
  -H 'Accept-Language: en-US,en;q=0.9' \
  -H 'Connection: keep-alive' \
  -H 'Content-Type: application/json' \
  -H 'Cookie: directus_session_token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjI5NTU2YzczLWJjOWItNDIzMy1iMjNjLWY5NWRhZGZmNzYzNSIsInJvbGUiOiI4YTg1YTY3Zi1iYzcwLTQzZDEtYTQwYi1kMTgzZTc5Nzg0MWYiLCJhcHBfYWNjZXNzIjp0cnVlLCJhZG1pbl9hY2Nlc3MiOnRydWUsInNlc3Npb24iOiJJbm9iQlBnZjNKRERJeHdYQ29neTVjYUh5LTdQWUI4OUc2NVBiUG14aWVpc0F5X2RwZnpxUVdjSzJ3TE96dEpfIiwiaWF0IjoxNzEzODk2NDAwLCJleHAiOjE3MTM5ODI4MDAsImlzcyI6ImRpcmVjdHVzIn0.86waMbiDPR2YUpuSq6HMqcw9NkrmyHOKs3pJZ6rLyMM' \
  -H 'DNT: 1' \
  -H 'Origin: http://localhost:8055' \
  -H 'Referer: http://localhost:8055/admin/content/articles' \
  -H 'Sec-Fetch-Dest: empty' \
  -H 'Sec-Fetch-Mode: cors' \
  -H 'Sec-Fetch-Site: same-origin' \
  -H 'User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/124.0.0.0 Safari/537.36' \
  -H 'sec-ch-ua: "Not-A.Brand";v="99", "Chromium";v="124"' \
  -H 'sec-ch-ua-mobile: ?0' \
  -H 'sec-ch-ua-platform: "macOS"' \
  --data-raw '{"query":{"limit":25,"filter":{"status":{"_neq":"archived"}},"search":null,"fields":["category","author","status","title","id","author.id","author.status","author.sort","author.user_created","author.date_created","author.user_updated","author.date_updated","author.title","category.id","category.status","category.sort","category.user_created","category.date_created","category.user_updated","category.date_updated","category.title","sort","user_created","date_created","user_updated","date_updated","note","user_updated.first_name","user_updated.last_name","user_updated.email","user_updated.password","user_updated.avatar","user_updated.location","user_updated.title","user_updated.description","user_updated.tags","user_updated.language","user_updated.tfa_secret","user_updated.email_notifications","user_updated.appearance","user_updated.theme_light","user_updated.theme_light_overrides","user_updated.theme_dark","user_updated.theme_dark_overrides","user_updated.status","user_updated.role","user_updated.token","user_updated.id","user_updated.last_page","user_updated.last_access","user_updated.provider","user_updated.external_identifier","user_updated.auth_data"],"sort":["id"]},"format":"csv","file":{"folder":null}}'

Directus Version

v10.10.5

Hosting Strategy

Self-Hosted (Custom)