CODEWITHSUNDEEP
mysql
Mirieri Mogaka
Mirieri Mogaka

Reputation: 561

Updating a stored JSON in Mysql Database

I am trying to update a Stored JSON in my database and i am unable to run the following update query. I have copied below a select statement and an update statement

SELECT 
`core_animal_event`. `animal_id` AS `MilkingEvent_animalID`, 
JSON_UNQUOTE(JSON_EXTRACT(`core_animal_event`.`additional_attributes`, '$."62"')) AS `MilkingEvent_milkCompositeLitres`, 
coalesce(JSON_UNQUOTE(JSON_EXTRACT(`core_animal_event`.`additional_attributes`, '$."68"')) + 
JSON_UNQUOTE(JSON_EXTRACT(`core_animal_event`.`additional_attributes`, '$."61"')) +
JSON_UNQUOTE(JSON_EXTRACT(`core_animal_event`.`additional_attributes`, '$."59"')))
FROM `core_animal_event` WHERE (`core_animal_event`. `event_type` = 2) AND (`core_animal_event`. `country_id` = '10');

UPDATE `core_animal`
SET
JSON_UNQUOTE(JSON_EXTRACT(`core_animal_event`.`additional_attributes`, '$."62"'))  = 
coalesce(JSON_UNQUOTE(JSON_EXTRACT(`core_animal_event`.`additional_attributes`, '$."68"')) + 
JSON_UNQUOTE(JSON_EXTRACT(`core_animal_event`.`additional_attributes`, '$."61"')) +
JSON_UNQUOTE(JSON_EXTRACT(`core_animal_event`.`additional_attributes`, '$."59"')))
 WHERE (`core_animal_event`. `event_type` = 2) AND (`core_animal_event`. `country_id` = '10')

The following is the sample data from which we have a stored JSON column called additional_attribute and animal_id which is unique

# animal_id, additional_attributes
'2576', '{\"59\": null, \"61\": null, \"62\": null, \"63\": null, \"64\": null, \"65\": null, \"66\": null, \"67\": null, \"68\": null, \"69\": \"1\", \"70\": \"2\", \"71\": \"1\", \"72\": null, \"73\": \"2\", \"74\": \"1\", \"75\": null, \"76\": null, \"77\": [\"1\"], \"78\": \"32\", \"79\": \"70\", \"80\": \"4\", \"81\": null, \"82\": null, \"83\": null, \"84\": \"Mkiwa\", \"85\": \"19280\", \"86\": \"2405\", \"87\": \"TNZ000192802405\", \"88\": \"Brownwhite\", \"89\": \"1565789020239.jpg\", \"90\": \"1565789049469.jpg\", \"96\": null, \"97\": null, \"98\": null, \"99\": \"1\", \"100\": null, \"101\": null, \"102\": null, \"103\": null, \"104\": null, \"105\": null, \"106\": null, \"107\": null, \"108\": null, \"109\": null, \"110\": null, \"111\": null, \"112\": null, \"113\": null, \"114\": null, \"115\": null, \"116\": null, \"117\": null, \"118\": null, \"119\": null, \"120\": null, \"121\": null, \"122\": null, \"123\": null, \"124\": null, \"125\": null, \"126\": null, \"127\": null, \"128\": null, \"129\": null, \"130\": null, \"131\": null, \"132\": null, \"133\": null, \"134\": null, \"135\": null, \"136\": null, \"137\": null, \"138\": null, \"139\": null, \"141\": null, \"142\": null, \"143\": null, \"144\": null, \"145\": null}'

The following is an example of a create statement

CREATE TABLE `core_animal_event` (
  `id` int NOT NULL AUTO_INCREMENT,
  `animal_id` int NOT NULL,
  `event_type` int NOT NULL,
  `additional_attributes` json DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `org_id` (`country_id`),
  KEY `animal_id` (`animal_id`),
  KEY `event_type` (`event_type`),
  CONSTRAINT `core_animal_event_ibfk_1` FOREIGN KEY (`animal_id`) REFERENCES `core_animal` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=941817 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT;

Upvotes: 1

Views: 1082

Answers (1)

A-Nicoladie
A-Nicoladie

Reputation: 286

Escape characters are not necessary here. I even think they may be the source of the problem.

Here's my code for the tests :

CREATE TABLE `core_animal_event` (
  `animal_id` int(11) NOT NULL AUTO_INCREMENT,
  `additional_attributes` json DEFAULT NULL,
  `event_type` int(11) NOT NULL,
  `country_id` int(11) NOT NULL,
  PRIMARY KEY (`animal_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

INSERT INTO core_animal_event
(animal_id, additional_attributes, event_type, country_id)
VALUES(1, '{ "59": null, "61": null, "62": null, "63": null, "64": null, "65": null, "66": null, "67": null, "68": null, "69": "1", "70": "2", "71": "1", "72": null, "73": "2", "74": "1", "75": null, "76": null, "77": ["1"], "78": "32", "79": "70", "80": "4", "81": null, "82": null, "83": null, "84": "Mkiwa", "85": "19280", "86": "2405", "87": "TNZ000192802405", "88": "Brownwhite", "89": "1565789020239.jpg", "90": "1565789049469.jpg", "96": null, "97": null, "98": null, "99": "1", "100": null, "101": null, "102": null, "103": null, "104": null, "105": null, "106": null, "107": null, "108": null, "109": null, "110": null, "111": null, "112": null, "113": null, "114": null, "115": null, "116": null, "117": null, "118": null, "119": null, "120": null, "121": null, "122": null, "123": null, "124": null, "125": null, "126": null, "127": null, "128": null, "129": null, "130": null, "131": null, "132": null, "133": null, "134": null, "135": null, "136": null, "137": null, "138": null, "139": null, "141": null, "142": null, "143": null, "144": null, "145": null}', 2, 10);

Without escape characters \, your SELECT query work.

For your update, here's an example :

UPDATE core_animal_event
SET additional_attributes = json_set(additional_attributes, '$."62"',
        COALESCE(JSON_UNQUOTE(JSON_EXTRACT(additional_attributes, '$."68"')) + 
            JSON_UNQUOTE(JSON_EXTRACT(additional_attributes, '$."61"')) +
            JSON_UNQUOTE(JSON_EXTRACT(additional_attributes, '$."59"')))
        )
WHERE (event_type = 2) AND (country_id = '10');

//-------

EDIT: Be careful when using JSON_EXTRACT and COALESCE function, in case all values are null, the returned value is 0, not a null value.

EDIT 2: Akina is right, your COALESCE function is no good... As you do, it's an addition (but maybe that's what you want...)

EDIT 3: If you want to use COALESCE here's an example:

SELECT animal_id AS colID, JSON_UNQUOTE(JSON_EXTRACT(additional_attributes, '$."62"')) AS col62, 
COALESCE (
    IF(JSON_TYPE(JSON_EXTRACT(additional_attributes, '$."68"'))='NULL', null, JSON_EXTRACT(additional_attributes, '$."68"')),
    IF(JSON_TYPE(JSON_EXTRACT(additional_attributes, '$."59"'))='NULL', null, JSON_EXTRACT(additional_attributes, '$."59"')),
    IF(JSON_TYPE(JSON_EXTRACT(additional_attributes, '$."61"'))='NULL', null, JSON_EXTRACT(additional_attributes, '$."61"'))
) AS result_of_coalesce
FROM core_animal_event WHERE (event_type = 2) AND (country_id = '10');

Upvotes: 2

Related Questions