CODEWITHSUNDEEP
mysqlsqlarraysjsonsql-insert
Assad Rajab
Assad Rajab

Reputation: 131

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

Upvotes: 4

Views: 74033

Answers (6)

Azeem Khan
Azeem Khan

Reputation: 110

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

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

Upvotes: 0

Francisco Hernández
Francisco Hernández

Reputation: 31

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)])

Upvotes: 3

Prashant Marathay
Prashant Marathay

Reputation: 313

@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.

Upvotes: 1

Sarath Chandra
Sarath Chandra

Reputation: 109

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

Upvotes: 0

GMB
GMB

Reputation: 222432

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

Upvotes: 17

Akina
Akina

Reputation: 42622

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

Upvotes: 1

Related Questions