How to save JSON array in MySQL with TypeORM

Introduction

This article is NestJS a record of the know-how I realized while developing through the framework. There may be a better way than the one I suggested. Suggestions are always welcome :)
NestJS - The framework TypeORMmanages the DB through the library. This article TypeORM deals with how to store JSON arrays in MySQL DB.

When we save 'array' in DB column?

Storing an array type in a DB column appears more often than you think.

Foreign Key Relation (OneToMany/ManyToOne)

Person Email Consider a case in which Entity and Entity exist for. At this time, since one person can have multiple emails, the relationship between the two is 'OneToMany/ManyToOne'. The MySQL table is stored as follows.

+----+--------------------+--------------+| id | person             | email_id(fk) |+----+--------------------+--------------+| 1  | Tom                | 1            || 2  | Evans              | 2, 3         |+----+--------------------+--------------++----+------------------+----------------+| id | email            | person_id(fk)  |+----+------------------+----------------+| 1  | [email protected]     | 1              || 2  | [email protected]   | 2              || 3  | [email protected] | 2              |+----+------------------+----------------+

Looking at the two tables, each has a foreign key called email_id and person_id. Person in the Evans case of , it has two emails in particular, so it has an email_id array of.
That is, you can find fk_id Array under the OneToMany/ManyToOne relationship.

simple-array

Even if it is not a foreign key, you can store arrays in MySQL columns. TypeORM helps simple-array you to store simple arrays in MySQL through.

@Column('simple-array')num_arr: number[];@Column('simple-array')str_arr: string[];

However , if you store it in a simple-array way string[], some problems arise. This is dealt with in more detail later.

How to save JSON array?

So far, we have looked at the case where an array is stored in MySQL. Now let's see how we can store JSON array in MySQL.

simple-array Let's try it!

@Column('simple-array')json_arr: JSON[];

@Column, simple-array and declared in the form of an array as before. Can this be saved? NOPE! In this case, an error occurs. Let's look at the error message.

DataTypeNotSupportedError: Data type "Array" in "example.json_arr" is not supported by "mysql" database.

?? Earlier we looked at the case where MySQL uses arrays. Array but now the error message says that the type is not supported by MySQL .
The error message is correct. MySQL Array does not support types. It's just giving us the feeling of supporting arrays in some way!!

simple-array Let's try it! - string[] version

Let's see how to circumvent the current problem. Our purpose is still to store JSON arrays in MySQL. But the JSON[] form is impossible, so string[] let's bypass the method we looked at earlier.

@Column('simple-array')json_arr: string[];

In javascript, it can be serialized in the form of through JSON.stringify() a function called . Our plan is as follows-

1. The API receives input as a JSON array.
2. Iterates through the received JSON array and applies to all JSON JSON.stringify() to serialize
3. string[] Save as MySQL
4. Get Whenever string[] a request comes in, the string in the string array stored as JSON.parse() is parsed and returned as

If you do this, you can convert the JSON array to a string array and store it in the DB without an error message.

But

string[] version issue

For those of us who store JSON, this method also has problems.
Let's say you have a JSON array like this:

[{name: 'Baker', job: 'musician'}, {name: 'Euler', job: 'mathematician'}]

JSON.stringify()If we change this to

["{name: 'Baker', job: 'musician'}", "{name: 'Euler', job: 'mathematician'}"]

becomes
Saving to DB works fine. However Get, if you check what is stored in the DB with ...

["{name: 'Baker'",  "job: 'musician'}", "{name: 'Euler'",  "job: 'mathematician'}"]

It has the following bizarre form... !!
The reason is that

MySQL pseudo-Array

We thought that MySQL supports number arrays or string arrays, even if JSON arrays cannot be stored. But That was TypeORM a trick of !!

Actually MySQL Array doesn't support types at all!! (Already confirmed with an error message.) So simple-array, how did we save it using properties?
simple-array stores all arrays as string. If it is a number array, "1, 2, 3", if it is a string array, "Bill, John, Baker". So, if even a comma (,) is inserted in the middle of the string, it is stored in the DB like this.

"'In other words, please be true', 'In other words, I love you'"

That is, even if it is a string array, only one string is stored in the DB. And parses the information of the column declared as TypeORM, based on the comma (,). simple-array therefore, even if you originally saved 2 strings, you will get 4 strings if you read the DB value.

Actually MySQL there is nothing wrong with it. TypeORM it is possible that the simple-array method of MySQL, especially in , is causing this problem. In other DBs, it may not be stored as a single string like this. 2 In MySQL, this is

In the case of JSON, commas (,) must appear to list objects, but in general strings, commas are rare. So TypeORM the developer may not have noticed. However, even such a small error can become a big obstacle or cause an error in development. (I lost half a day because of this...)

[Solution] Just save it as a string!

Through several trials and errors, simple-array I confirmed how TypeORM stores arrays in MySQL...
So let's use this to reorganize our code.

@Column()json_arr: string;

Column is simply string set to .

Each JSON in the JSON array JSON.stringify() is serialized as a function. Then, they JSON.parse() are transformed into a possible form and stored.

// save JSON array into MySQL DBsaveJSONArray(json_arr: JSON[]) {  let jsonArr_string = "[";  for(let i=0; i < json_arr.lenght; i++){    jsonArr_string += JSON.stringify(json_arr[i]);    if(i < json_arr.lenght-1){      jsonArr_string += ",";    }  }  jsonArr_string += "]";  this.yourRepository.save({ json_arr: jsonArr_string });}// load JSON array from MySQL DBasync findJSONArray(): Promise<YourEntry[]> {  const entry_arr = await this.yourRepository.find();  for(let i=0; i < entry_arr.lenght; i++){    entry_arr[i].json_arr = JSON.parse(entry_arr[i].json_arr);  }  return entry_arr;}

I think the best way is json_arr: string to set it to , parse the DB value to , and return it as JSON. JSON.parse() (Of course, someone may have found a better way than this and put it to good use!)