CODEWITHSUNDEEP
sqlarraysjsonsql-updatemariadb
user1786639
user1786639

Reputation:

MariaDB JSON add number to value

I have table:

CREATE TABLE IF NOT EXISTS `t2` (
  `j` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`j`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `t2` (`j`) VALUES
    ('{"sdsd": 123, "qqq": 456, "data": [{"www": 789, "eee": 98}, {"ddd": 578}]}');

And it's need update json value "www" to +10. I try did it like:

UPDATE t2 set j = JSON_SET(j, "$.data[0].www", "$.data[0].www" +1) ;

But get an error:

SQL Errro (1292): Truncated incorrect DOUBLE value: '$.data[0].www'

Please help, how correct update the value? Thanks!

Upvotes: 0

Views: 72

Answers (1)

GMB
GMB

Reputation: 222482

You are giving a JSON path as a third argument to json_set(), while you mean to retrieve the value stored at that path - for this, you can use json_extract():

update t2 
set j = json_set(
    j, 
    '$.data[0].www', 
    json_extract(j, '$.data[0].www') + 1 
);

Demo on DB Fiddlde:

| j                                                                          |
| :------------------------------------------------------------------------- |
| {"sdsd": 123, "qqq": 456, "data": [{"www": 790, "eee": 98}, {"ddd": 578}]} |

Note: this works because your numeric values are JSON numbers (that is, they are not surrounded with double quotes). If you have JSON strings instead, then you need to unquote them first:

update t2 
set j = json_set(
    j, 
    '$.data[0].www', 
    json_unquote(json_extract(j, '$.data[0].www')) + 1 
);

Upvotes: 0

Related Questions