SQL Insert JSON into table column

while searching for a way to insert array into single db columns, I found an article about inserting JSON string. However it wasn’t explained how. I tried to search and find the way with no success.

I have the following table:

+---------+----------------+----+
|  Name   |      Type      |    |
+---------+----------------+----+
| id      | int            | AI |
| name    | String         |    |
| address | JSON(longtext) |    |
+---------+----------------+----+

What i want to do is insert a Json arry in the address column. like:

+----+-----------+------------------------------------------+
| id |   name    |                 address                  |
+----+-----------+------------------------------------------+
|  1 | User name | [{street: "street address", city: "Berlin"}] |
+----+-----------+------------------------------------------+

I thought about inserting the JSON as String but im not sure if this a good idea. Any suggestions?

Answer

You can pass your data as a string, as long as it is valid JSON; MySQL will happily convert it for you under the hood.

insert into mytable (id, name, address)
values (
    1,
    'User name',
    '[{"street": "street address", "city": "Berlin"}]'
);

An alternative is to use JSON builder functions:

insert into mytable (id, name, address)
values (
    1,
    'User name',
    json_array(json_object('street', 'street address', 'city', 'Berlin'))
);