CODEWITHSUNDEEP
jsonnestedsql-updatemariadbmariadb-10.3
Subhankar Majumder
Subhankar Majumder

Reputation: 33

Update nested Json in Mysql/Mariadb

{
  "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 |
+--------------------------------------------+

Upvotes: 1

Views: 2359

Answers (2)

b.s
b.s

Reputation: 2755

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

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65278

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

Upvotes: 1

Related Questions