CODEWITHSUNDEEP
jsonmariadb
pvjhs
pvjhs

Reputation: 699

Alternative of wildcard in JSON_SET in mariadb

I have a table as

create table table_name { id int, class json };

insert into class(id, class) values (1, '{ "class" : 1, "student" : [ { "student_id" : "10", "name" : "A" }, { "student_id" : "5", "name" : "B" } ] }' );

Now i have to update name to "bbb" of student where "student_id" = 5. As we can see students is a list of student documents/object. How to use JSON_SET or anything else to achieve the goal as JSON_SET doesnt support wildcard(*) in its path.

Upvotes: 1

Views: 556

Answers (1)

wchiquito
wchiquito

Reputation: 16551

One option is:

MariaDB [_]> DROP TABLE IF EXISTS `table_name`;
Query OK, 0 rows affected (0.00 sec)

MariaDB [_]> CREATE TABLE IF NOT EXISTS `table_name` (
    ->   `id` SERIAL,
    ->   `class` JSON NOT NULL,
    ->   CHECK (JSON_VALID(`class`))
    -> );
Query OK, 0 rows affected (0.00 sec)

MariaDB [_]> INSERT INTO `table_name` (`class`)
    -> VALUES ('{"class": 1, "student": [{"student_id": "10", "name": "A"}, {"student_id": "5", "name": "B"}]}');
Query OK, 1 row affected (0.00 sec)

MariaDB [_]> SELECT `id`, `class`
    -> FROM `table_name`;
+----+------------------------------------------------------------------------------------------------+
| id | class                                                                                          |
+----+------------------------------------------------------------------------------------------------+
|  1 | {"class": 1, "student": [{"student_id": "10", "name": "A"}, {"student_id": "5", "name": "B"}]} |
+----+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [_]> UPDATE `table_name`
    -> SET `class` = 
    ->   JSON_SET(
    ->     `class`,
    ->     REPLACE(
    ->       JSON_UNQUOTE(
    ->         JSON_SEARCH(`class`,
    ->                     'one',
    ->                     5,
    ->                     NULL,
    ->                     '$.student'
    ->                    )
    ->       ),
    ->     'student_id',
    ->     'name'
    ->     ),
    ->     'bbb'
    ->   )
    -> WHERE `id` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [_]> SELECT `id`, `class`
    -> FROM `table_name`;
+----+--------------------------------------------------------------------------------------------------+
| id | class                                                                                            |
+----+--------------------------------------------------------------------------------------------------+
|  1 | {"class": 1, "student": [{"student_id": "10", "name": "A"}, {"student_id": "5", "name": "bbb"}]} |
+----+--------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

See dbfiddle.

Upvotes: 1

Related Questions