Updating just one attribute in existing documents in existing collection
Good day ,
I have a big CSV file with 2 fields: article_number and reference.
I have a big collections with these 2 fields and many others. Any of the 2 refered fields is not the key field or _id field.
I need to search all the references in the collection wich match with the article_number field in order to update the field reference.
Is it possible with DataGrip? How so?
Best regards ,
Jose M.
Please sign in to leave a comment.
Thank you for contacting us.
When you're saying having collections of the columns, values of which are also presented in a CSV file, are these collections also files, or were you referring to tables in a database? Also, do you want to import this data into a table and then build a table relationship, such as private key - foreign key, to make the data consistent?
Please let me know so I can assist you with your concerns.
Hi Aleksandr ,
I need to make an import operation from data inside a huge CSV file with more than 1 million rows x 2 columns. Column 1 is a subkey called EAN and column 2 is an attribute called REFERENCE and it is the data to update. Inside the CSV file, the EAN value is unique.
I need to update data from column 2 of CSV file in an existing database, in an existing collection, in some existing documents. All the other attrbiutes in the document in the database must remain as they were.
It is not neccesary to build any special relationship inside the existing documents.
The main key in the database documents is the _id and it is an ObjectId.
There is an attribute inside the database documents called EAN which is a string.
The subkey I have to update the data from the CSV file is the EAN, as I do not have the _id.
So
CSV file: columns EAN and REFERENCE.
Database documets. fields: _id, EAN, REFERENCE and a lot more.
I need to search the documents in the database with the refered EAN and upodate its REFERENCE.
Is it possible to make the operation? How so?
Best regards ,
Jose M.
Your use-case scenario can be achieved through a set of actions, depending on the SQL Database and Table definitions you have. As you have unique columns in your table and there's no property such as Identity or any other generating a unique ID for every new column you add to a table, this could cause errors due to rows having already existing values you try to import from a CSV file. So in this case, our Import feature will just insert the columns and it won't do any updates. Also, a CSV import is carried out for a specific table only so you need to apply this for every table having a need to update values of specific columns.
That being said, you'll likely need to 1. create a temporary table where you'd import the data from CSV and 2. compare these 2 tables and update respectively. I am afraid I don't see ways of how this task could be achieved in one go.
Let me know if you have questions.
Hi Aleksandr ,
Thank you for your message.
Referring to the database: the database I am using, it is MongoDB, not MySQL. That's why I talk about collections, documents and attributes, and not tables, rows and fields, although it is true that they are equivalent.
Referring to the source file: it is a CSV as it is how I got the data. I cannot transform it into JSON. It is not possible to do this using free tools, given the large size of the file. But I am able to enrich the file in order to make viable the importation.
Referring to the match of the documents, I can enrich the CSV file, so it will have the columns:
* _id = ObjectId of the document in the target collection. This attribute uniquely identifies the document within the collection.
* ean = string. As I have the _id this attribute is no longer needed.
* reference: The attribute to update in the target collection, with overwrite instruction. This means that is the field is previously to the import filled with data, it will be overwritten.
With this rich CSV file, is it possible to do the import to locate each document in the collection precisely and update only 1 attribute of this document, and do it massively for all the matching documents in the collection?
Best regards ,
Jose M.
Appreciate you getting more into detail with your scenario.
Unfortunately, we don't support data Import from external files such as CSV into documents in Mongo DB. This feature is not available for this particular database engine.
Let me know if you have other questions.
Also upvote the feature request https://youtrack.jetbrains.com/issue/DBE-13643/Support-importing-data-to-MongoDB