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?

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
– Sarath Chandra, Commented Dec 12, 2020 at 19:14
The JSON you show is not valid JSON. You need to delimit keys with double-quotes. — Bill Karwin
– Bill Karwin, Commented Dec 12, 2020 at 19:17

GMB · Accepted Answer · 2020-12-12 19:15:30Z

17

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

edited Dec 12, 2020 at 19:15

answered Dec 12, 2020 at 19:12

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Francisco Hernández · Accepted Answer · 2022-11-25 08:08:36Z

3

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, 2022 at 8:08

answered Nov 25, 2022 at 8:06

Francisco Hernández

312 bronze badges

Comments

Akina · Accepted Answer · 2020-12-12 19:12:24Z

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, 2020 at 19:12

Akina

43.2k6 gold badges17 silver badges30 bronze badges

1 Comment

Assad Rajab Over a year ago

Thank you for your replay. much appreciate it

Prashant Marathay · Accepted Answer · 2022-08-14 17:10:07Z

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, 2022 at 17:10

Prashant Marathay

3133 silver badges10 bronze badges

1 Comment

Assad Rajab Over a year ago

Tbh. This is the answer I was looking for :). The other answer is also good and working.

Sarath Chandra · Accepted Answer · 2020-12-12 19:20:46Z

0

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, 2020 at 19:20

Sarath Chandra

1191 silver badge8 bronze badges

1 Comment

Assad Rajab Over a year ago

Thank you man. i appreciate your replay and expain

Azeem Khan · Accepted Answer · 2024-07-10 15:42:18Z

0

INSERT INTO TABLE_NAME (STREET, CITY) VALUES (?,?),[STREET,CITY]

For best practice you can utilize the above mentioned format for handling dynamic data

edited Jul 10, 2024 at 15:42

answered Jul 9, 2024 at 23:49

Azeem Khan

1106 bronze badges

Collectives™ on Stack Overflow

SQL Insert JSON into table column

6 Answers 6

Comments

Comments

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related