mysql 5.7 appending key/value to nested json object

Question

I'm trying the new API for mysql 5.7 which deals with JSON columns. My test column looks like this:

{"foo":{"efg":1}, "bar":{"abc":0}}

What I would like to do is append to one of the keys, for example foo so it will result in "foo":{"efg":1, "klm":2}. What I've tried so far following their documentation:

mysql> select json_insert(test, '$.foo', 10, '$.foo.klm', 2) from table1
       where name='Joe';

What that does is replace "efg":1 and the result is "foo":{"klm":2}.

mysql> select json_array_append(test, '$.foo', '{"klm":2}') from table1 where
       name="Joe';

The above line obviously converts foo into an array "foo":[{"efg":1}, {"klm":2}], which is not what I want.

I've tried combining queries together:

mysql> select json_insert(test, '$.foo', 10, '$.foo', 
       select json_merge(select json_extract(test, '$.foo') from table1 
       where name="Joe"), '{"klm":2}') from table1 where name="Joe";

That just gives me a syntax error near select json_extract(test, '$.foo').

Any advice would be much appreciated.

@JoachimIsaksson the desired result should be {"foo":{"efg":1, "klm":2}, "bar":{"abc":0}}. Their documentation mostly works with modifying arrays but I would like to keep everything as an Object. — denikov
– denikov, Commented Mar 28, 2016 at 14:39
I get that exact result using your json_insert, (or just select json_insert(test, '$.foo.klm', 2) from table1 where name='Joe'; "efg":1 does not disappear for me. — Joachim Isaksson
– Joachim Isaksson, Commented Mar 28, 2016 at 14:41
@JoachimIsaksson I'm not sure where the problem is then. I emitted some parameters, like in your comment, and it still replaces everything for the "foo" Object. — denikov
– denikov, Commented Mar 28, 2016 at 14:49
How about select json_merge(test, '{"foo":{"klm":2}}') FROM table1;? Still dropping efg? — Joachim Isaksson
– Joachim Isaksson, Commented Mar 28, 2016 at 14:56
@JoachimIsaksson still dropping. The only way to keep it is explicitly adding all the values, as in select json_insert(test, '$.foo.efg', 1, '$.foo.klm', 2) from... — denikov
– denikov, Commented Mar 28, 2016 at 15:01

wchiquito · Accepted Answer · 2016-03-28 15:36:11Z

14

I can't reproduce the problem.

Test:

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.11    |
+-----------+
1 row in set (0.00 sec)

mysql> SET @`test` := '{"foo": {"efg":1}, "bar": {"abc":0}}';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT JSON_INSERT(@`test`,/*'$.foo', 10,*/ '$.foo.klm', 2);
+--------------------------------------------------+
| JSON_INSERT(@`test`, '$.foo.klm', 2)             |
+--------------------------------------------------+
| {"bar": {"abc": 0}, "foo": {"efg": 1, "klm": 2}} |
+--------------------------------------------------+
1 row in set (0.00 sec)

UPDATE

mysql> DROP TABLE IF EXISTS `table1`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `table1` (
    -> `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> `test` JSON
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `table1`
    ->     (`test`)
    -> VALUES
    ->     ('{"foo": {"efg":1}, "bar": {"abc":0}}');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT `id`, `test` FROM `table1`;
+----+----------------------------------------+
| id | test                                   |
+----+----------------------------------------+
|  1 | {"bar": {"abc": 0}, "foo": {"efg": 1}} |
+----+----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_INSERT(@`test`, '$.foo.klm', 2)
    -> FROM `table1`
    -> WHERE `id` = 1;
+--------------------------------------------------+
| JSON_INSERT(@`test`, '$.foo.klm', 2)             |
+--------------------------------------------------+
| {"bar": {"abc": 0}, "foo": {"efg": 1, "klm": 2}} |
+--------------------------------------------------+
1 row in set (0.00 sec)

mysql> UPDATE `table1`
    -> SET `test` = JSON_INSERT(@`test`, '$.foo.klm', 2)
    -> WHERE `id` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT `id`, `test` FROM `table1`;
+----+--------------------------------------------------+
| id | test                                             |
+----+--------------------------------------------------+
|  1 | {"bar": {"abc": 0}, "foo": {"efg": 1, "klm": 2}} |
+----+--------------------------------------------------+
1 row in set (0.00 sec)

edited Mar 28, 2016 at 15:36

answered Mar 28, 2016 at 15:14

wchiquito

16.7k2 gold badges37 silver badges46 bronze badges

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

4 Comments

denikov Over a year ago

When I executed word for word from your example, it worked correctly. So maybe initially added the Object incorrectly into the row? I added it directly inside insert into table1 (test) values ('{"foo":{"efg":1}, "bar":{"abc":0}}');. Is that the problem?

wchiquito Over a year ago

@denikov: Updated answer.

denikov Over a year ago

Thanks for the update. I'll try it in a couple hours and hopefully figure it out. Thanks for helping.

denikov Over a year ago

Still have no idea what wrong, only the update statement does what it's supposed to.

Collectives™ on Stack Overflow

mysql 5.7 appending key/value to nested json object

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related