MySQL JSON object insert

Question

I have the following JSON:

 {
    "params" : {
        "A" : 200.5,
        "B" : 70.2
    }
 }

And the following table:

CREATE TABLE `params` (
   `param` varchar(255),
   `value` float
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Is there a way to make mysql function with one insert query without using "while do" directly to insert all the parameters into table like this:

 -------+-------
| param | value |
|-------+-------|
|     A |  200.5|
|     B |   70.2|
 ---------------

You cannot do this directly in MySql. Only you can store complete Json in one tuple. — Santosh Vishwakarma
– Santosh Vishwakarma, Commented Nov 25, 2019 at 11:23

GMB · Accepted Answer · 2019-11-25 10:55:12Z

1

If you are running MySQL 8.0, you can use json_keys() to dynamically extract the keys from the json subobject as a json array, and then use json_table() to turn it to rows. You can then extract the values.

Consider:

insert into `params`
with t as (select '{"params": { "A": 200.5, "B": 70.2 } }' js)
select x.k, json_extract(js, concat('$.params.', x.k)) v
from 
    t
    cross join json_table(
        json_keys(js->"$.params"),
        "$[*]" columns(k varchar(255) path "$")
    ) as x

Demo on DB Fiddle

Content of the table after running the query:

param | value
:---- | ----:
A     | 200.5
B     |  70.2

answered Nov 25, 2019 at 10:55

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Comments

Vinod Sai · Accepted Answer · 2019-11-25 11:19:20Z

0

We can insert multiple rows with a single query using insert into. we need to construct this query using your JSON object

INSERT INTO params(param,value)
VALUES('A',200.5), ('B', 70.2);

answered Nov 25, 2019 at 11:19

Vinod Sai

2,1323 gold badges17 silver badges26 bronze badges

Collectives™ on Stack Overflow

MySQL JSON object insert

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related