How do I insert new values into a mysql json array?

Question

I am testing this example.

I understand that t1 is the table and people the array but where do I put the name of the column?

INSERT INTO t1 VALUES (
        '{ "people": [
            { "name":"John Smith",  "address":"780 Mission St, San Francisco, CA 94103"}, 
            { "name":"Sally Brown",  "address":"75 37th Ave S, St Cloud, MN 94103"}, 
            { "name":"John Johnson",  "address":"1262 Roosevelt Trail, Raymond, ME 04071"}
         ] }'
    );

They call the column json_col but they only use it in a select query like this.

SELECT people.* 
FROM t1, 
     JSON_TABLE(json_col, '$.people[*]' COLUMNS (
                name VARCHAR(40)  PATH '$.name',
                address VARCHAR(100) PATH '$.address')
     ) people;

json_col is the name of the column ( CREATE TABLE t1(json_col JSON); ). For insertion, you can also use INSERT INTO t1(json_col) VALUES (...) — Pavel Třupek
– Pavel Třupek, Commented Jan 23, 2019 at 12:51
I've also added this comment as answer, co it will be more visible :) — Pavel Třupek
– Pavel Třupek, Commented Jan 23, 2019 at 13:42

Pavel Třupek · Accepted Answer · 2019-01-23 13:41:00Z

1

json_col is the name of the column ( CREATE TABLE t1(json_col JSON); ).

For insertion, you can also use INSERT INTO t1(json_col) VALUES (...)

answered Jan 23, 2019 at 13:41

Pavel Třupek

9187 silver badges22 bronze badges

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

2 Comments

Peter Karlsson Over a year ago

How do I update the same array after the new value is inserted? So I can use a where clause on for example an id column.

Pavel Třupek Over a year ago

Exactly, json_col (you can name the column as you want) is just a regular column with data type JSON.

Collectives™ on Stack Overflow

How do I insert new values into a mysql json array?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related