How to save JSON array in MySQL with TypeORM
Tags: javascript, typeorm, mysql
Published: 2022-01-28 16:54:30

## 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 `TypeORM `manages 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  | tom@mail.com     | 1              |
| 2  | evans@mail.com   | 2              |
| 3  | evans@univ.ac.kr | 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 DB
saveJSONArray(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 DB
async 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!)