Importing data (csv, json) into a collection with a foreign key adds data to the related table, creating duplicates

[BI-MarcB]

Describe the Bug

There seems to be an error with csv/json imports when relations show non the primary key column in the UI. It looks like Directus does not do a look-up in the referenced collection to find valid PKs, but instead always defaults to inserting in the the related collection. That is, even if the values already exist.

To Reproduce

Create collection B with two fields: 'id' (PK) and 'name'. Create two rows: id 1 "example X" and id 2 "example Z"

Create collection A with two fields: 'id' (PK) and a relation to collection B. In the UI, hide B.id and show B.name.

Create a dummy entry in collection A referencing 'example X' in collection B.

Remove all fields from UI except A.id and B.name

Export the data as JSON via the web UI: Export field A.id and field B.name.

Access the json and change id to 2. JSON data after the change:

Import the json.

Expected result: Directus will lookup existing values on collection B for field B.name and find that id 1 exists with a value of "example X". If that is not possible, Directus should throw an error.

Actual result: Directus creates a new row in collection B with the values id 3 'example X'. This is not only a relationship bug, but especially unexpected as an import to collection A should never create values in collection B without confirmation.

This also happens with csv exports.

Directus Version

v10.8.3

Hosting Strategy

Self-Hosted (Docker Image)

[br41nslug]

This is correct, the import does not magically look up the corresponding primary key. It expects the primarykey/foreignkey (or complete object for creation) to be the one imported. Make sure to export the actual value not the display label when exporting for re-import.

Feel free to create a feature request for this 😄

[BI-MarcB]

Are there any docs for this? Especially regarding the entity creation in the related collection?

I think it is worrisome that interface settings have an impact on import/export behaviour, especially without any confirmation.

[br41nslug]

https://docs.directus.io/user-guide/content-module/import-export.html#import-export
https://docs.directus.io/reference/system/utilities.html#import-data-from-file

The import and export function of the API behave no different than updating/creating and reading in the rest of the API but with files as input/output instead of a http response -> https://docs.directus.io/reference/introduction.html#relational-data

I think it is worrisome that interface settings have an impact on import/export behaviour

Only if you're using the web App instead of the API to export then yes the export interface will default to your currently selected fields and filters (unless you change that to the desired fields when exporting, it's just a pre-fill). Not all export usecases are meant to be re-imported into the same system (think external systems)

especially without any confirmation.

why would you need confirmation on a manual export action? confirmation of what? you get presented with all the options you're about to export which you really should check/confirm are wanted before pressing the export button

[BI-MarcB]

From the linked docs, it is not clear that an import of relational data will create data in referenced collections. There is also no mention in the import/export docs to check the related API docs.

why would you need confirmation on a manual export action?

This was referring to the import. An import in collection A creating an entry in collection B without confirmation is unexpected. Especially as 'name' looks like a valid FK in the frontend and in the export.

[joehannouch]

I have the same scenario where i exported a collection to see how it looks, but i'm not able to fill the csv (excel) manually with collection_id.name since new rows will be created when i import the file, i "have" to use the ids, which is not practical at all, i was considering sending the file to an editor for him to fill around 1000 entries with M2M by name, It's just not possible to ask of them to insert the M2M (category in my case) by UDID, it's just not readable by a human:

This works but is not readable:
"[{""collection_id"":{""id"":""a854913b-8b6d-48c9-9e75-0610871a29e9""}}]"

This creates a new collection (even :
"[{""collection_id"":{""name"":""Tech""}}]"

I know that the first one works since id is the primary key, but i find it counter intuitive to have to process and parse the csv and build a json out of it in order to use that for the import (at least this is what i'm planning to do but still debating the decision).
Maybe i can make the title as a primary key in this instance?