SQL Insert JSON into table column

Question

While searching for a way to insert array into single DB columns, I found an article about inserting JSON strings. 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 array 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 I'm not sure if this a good idea. Any suggestions?

Answer 1

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'))
);