Updateing mysql json field in array

Question

Having this json:

{
    f1: "abc",
    f2: [
        {id: 1, val:"a"},
        {id: 2, val:"b"},
        {id: 3, val:"c"}
    ],
    f3: [
        "a",
        "b",
        "c"
    ]
}

Update: As an example:

SELECT JSON_SEARCH(
'{"f1": "abc", "f2": [{"id": "1", "val":"a"},{"id": "2", "2":"b"},{"id": 3, "val":"2"}], "f3": ["a","b","c"]}',
'all', '1', null, '$.f2[*].id');

returns the needed path for f2->id==1 I can then use

select json_set(
'{"f1": "abc", "f2": [{"id": "1", "val":"a"},{"id": "2", "2":"b"},{"id": 3, "val":"2"}], "f3": ["a","b","c"]}',
'$.f2[0].val', 'd');

to update the data.

but

SELECT JSON_SEARCH(
'{"f1": "abc", "f2": [{"id": 1, "val":"a"},{"id": "2", "2":"b"},{"id": 3, "val":"2"}], "f3": ["a","b","c"]}',
'all', '1', null, '$.f2[*].id');

won't find f2->id==1 . Neither does

SELECT JSON_SEARCH( 
'{"f1": "abc", "f2": [{"id": 1, "val":"a"},{"id": "2", "2":"b"},{"id": 3, "val":"2"}], "f3": ["a","b","c"]}',
'all', 1, null, '$.f2[*].id');

=========

update 2: I will just save the IDs as string... But right now i have onother problem:

SELECT JSON_SEARCH(
'{"mm": [{"id":"1","field":"test","value":33}]}',
'one', '1', null, '$.mm[*].id') as path;

is working

SELECT *  FROM document_data where document_id=5;
update document_data set data=JSON_SET(data, '$.mm', json_array()) where document_id=5;
update document_data set data=JSON_ARRAY_APPEND(data, '$.mm', '{"id":"1","field":"test","value":33}') where document_id=5;
SELECT JSON_SEARCH(data, 'one', '1', null, '$.mm[*].id') as path from document_data where id='5';

is not working. Seems to be the quoting. Can someone help?

==========

how can i update f2 where id==2 using JSON_SET?

Tried everything but i can't seem to figure out how to do it.

Many thanks Rene

If you tried everything then at least post one attempt you did. Post your query that doesn't work so that we may help you to get it working. See here — ProEvilz
– ProEvilz, Commented Nov 16, 2017 at 12:45
Your question is not clear and I can't reproduce the new problem you mention, see db-fiddle. — wchiquito
– wchiquito, Commented Nov 17, 2017 at 10:16

wchiquito · Accepted Answer · 2017-11-16 18:49:19Z

JSON_SEARCH, by design, seems to search only strings, see WL#7909: Server side JSON functions :: JSON_SEARCH.

One option, very unintuitive is to use something like (be careful with performance problems):

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

mysql> SET @`json` := '{
    '>   "f1": "abc",
    '>   "f2": [
    '>           {"id": 1, "val": "a"},
    '>           {"id": 2, "val": "b"},
    '>           {"id": 3, "val": "c"}
    '>         ],
    '>   "f3": ["a", "b", "c"]
    '> }',
    ->     @`value` := 2,
    ->     @`base_path` := '$.f2';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT JSON_SEARCH(
    ->   REPLACE(
    ->     REPLACE(
    ->       REPLACE(
    ->         JSON_EXTRACT(@`json`, CONCAT(@`base_path`, '[*].id')),
    ->       ', ', '","'),
    ->     '[', '["'),
    ->   ']', '"]'),
    -> 'one', @`value`) INTO @`path`;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @`path`;
+---------+
| @`path` |
+---------+
| "$[1]"  |
+---------+
1 row in set (0.00 sec)

mysql> SELECT
    ->   CONCAT(
    ->     REPLACE(
    ->       JSON_UNQUOTE(@`path`),
    ->       '$',
    ->       @`base_path`
    ->     ),
    ->   '.val') INTO @`path`;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @`path`;
+-------------+
| @`path`     |
+-------------+
| $.f2[1].val |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_SET(@`json`, @`path`, 'd');
+-------------------------------------------------------------------------------------------------------------------+
| JSON_SET(@`json`, @`path`, 'd')                                                                                   |
+-------------------------------------------------------------------------------------------------------------------+
| {"f1": "abc", "f2": [{"id": 1, "val": "a"}, {"id": 2, "val": "d"}, {"id": 3, "val": "c"}], "f3": ["a", "b", "c"]} |
+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

See db-fiddle.

Thanks for this. but that is definitly not what i want ... I will just save the IDs as strings. But i added some more comment. MySQL and JSON does not work that nice it seems ...

Collectives™ on Stack Overflow

Updateing mysql json field in array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related