Update nested Json in Mysql/Mariadb

Question

{
  "people": {
    "Man": {
      "Employee": "50",
      "Student": "91",
      "Artist": "80",
      "Clark": "50"
    },
    "Woman": {
      "Employee": "21",
      "Student": "01",
      "Artist": "00",
      "k3": "30",
      "Clark": "68"
    }
  }
}

How can I Update Employee Value From Man Object. I want something similar to below mysql query.

UPDATE TABLE 
   SET Column = JSON_SET(Column, '$.people.Man.Employee', '51') 
  WHERE Id=1234567890;

MariaDB [**********]> SELECT VERSION();

+--------------------------------------------+
| VERSION()                                  |
+--------------------------------------------+
| 10.3.17-MariaDB-1:10.3.17+maria~bionic-log |
+--------------------------------------------+

Barbaros Özhan · Accepted Answer · 2020-02-18 11:00:48Z

1

Firstly, fix the keys within the JSON column by adding quotes for each, and values by adding quotes for some which starts with zeroes and go on with other digits.

Then apply :

UPDATE tab
   SET Col = JSON_SET(
       Col,
       "$.people.Man.Employee", "50",
       "$.people.Man.Employee", "51"
       )
 WHERE ID = 1234567890;

Demo

edited Feb 18, 2020 at 11:00

answered Feb 18, 2020 at 10:53

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

1 Comment

Subhankar Majumder Over a year ago

Thank You Very Much. This is perfectly Worked.

b.s · Accepted Answer · 2022-07-28 18:57:30Z

0

Even, I don't see any problem with the following

UPDATE tab
SET 
    Col = JSON_SET(Col, '$.people.Man.Employee', '51')
WHERE
    Id = 1234567890;

Demo

answered Jul 28, 2022 at 18:57

b.s

2,7652 gold badges19 silver badges28 bronze badges

Collectives™ on Stack Overflow

Update nested Json in Mysql/Mariadb

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related