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 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?

No don't convert the json to string. You already have json datatype in MySQL from 5.7 version. Insert the column directly. It accepts. — Sarath Chandra, Dec 12 '20 at 19:14
The JSON you show is not valid JSON. You need to delimit keys with double-quotes. — Bill Karwin, Dec 12 '20 at 19:17

GMB · Accepted Answer · 2020-12-12T19:15:30.130

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

Francisco Hernández · Answer 2 · 2022-11-25T08:08:36.980

2

This works in my case. In summary use ARRAY[CAST('{"key": "val"}' as json)].

Example:

insert into <table_name> (id, name, address)
values ('1', 
        'User name', 
         array[cast('{"street": "street address", "city": "Berlin"}' as json)])

edited Nov 25 '22 at 08:08

answered Nov 25 '22 at 08:06

Francisco Hernández

21
2

score 1 · Answer 3 · answered Dec 12 '20 at 19:12

1

CREATE TABLE people (id INT, name VARCHAR(255), address JSON);

INSERT INTO people (id, name, address)
VALUES (1, 'User name', '[{"street": "street address", "city": "Berlin"}]');

SELECT *
FROM people;

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

db<>fiddle here

answered Dec 12 '20 at 19:12

Akina

39,301
5
14
25

Thank you for your replay. much appreciate it – Assad Rajab Dec 12 '20 at 19:26

score 1 · Answer 4 · answered Dec 12 '20 at 19:20

1

Why converting to String if you already have a datatype JSON in MySQL

INSERT INTO <Table_Name> VALUES ('{street: "street address", city: "Berlin"}');

This insert statement will directly inserts your array. There is no need to convert.

See this stackoverflow answer

answered Dec 12 '20 at 19:20

Sarath Chandra

81
1
6

Thank you man. i appreciate your replay and expain – Assad Rajab Dec 12 '20 at 19:27

score 1 · Answer 5 · answered Aug 14 '22 at 17:10

1

@GMB's alternative answer looks correct but I thought I would add another answer to show how I implemented this in a format that I prefer:

INSERT INTO <table name>  
SET field1 = '123',
    field2 = json_array(‘456’);

I like listing my field names next to their values so I don't have to mentally map the values to the variables.

Also, I didn't need the json_object since I was not entering a key:value pair - I was just entering a list of values.

answered Aug 14 '22 at 17:10

Prashant Marathay

185
2
8

Tbh. This is the answer I was looking for :). The other answer is also good and working. – Assad Rajab Aug 15 '22 at 20:29

SQL Insert JSON into table column

5 Answers5