Mysql - Extract json -multiply with one of the attribute-CodePudding

I have json array like

[{"name":"MyName","age":10.0},{"name":"yourName","age":20.0}]

stored in MySql. Now the task is , I want to multiply by say 2 with all the age attribute, so the result json would be

[{"name":"MyName","age":20.0},{"name":"yourName","age":40.0}]

and update this newly generated json to some other column in the same record. Any help? Thanks in advance.

CodePudding user response：

As suggested by @nbk, using JSON_TABLE.

update test_json tj
set json_2 = 
(
select json_arrayagg(json_object("name",name, "age",age)) 
from 
(
select t.id, p.name, p.age * 2 age
from test_json t, 
json_table(json_object("anytext",json_1),
'$.anytext[*]'
columns (
  name varchar(30) PATH '$.name',
  age int PATH '$.age' )
) p
) d
where d.id = tj.id
group by id
) ;

Refer fiddle here.

It is working in current form, but you might need to add additional conditions to update after set such as where exists.

Only the select part -


select id, json_arrayagg(json_object("name",name, "age",age)) orig_json,
json_arrayagg(json_object("name",name, "age",newage)) new_json
from 
(
select t.id, p.name, p.age age, p.age * 2 newage
from test_json t, 
json_table(json_object("anytext",json_1),
'$.anytext[*]'
columns (
  name varchar(30) PATH '$.name',
  age int PATH '$.age' )
) p
) d
group by id ;

updated fiddle.

CodePudding user response：

With mysql 5,7 it grets a little more complicated. adapated from here https://stackoverflow.com/a/42153230/5193536

I added all steps so you can undertsand the orocess, of extracting and agian jsonfy the the result.

The last select only shows the the result as text, so that you can see the changes

CREATE TABLE t1 (rec_num INT, jdoc JSON);

INSERT INTO t1 VALUES 
  (1, '[{"name":"MyName","age":10.1},{"name":"yourName","age":20.0}]'), 
  (2, '[{"name":"MyName2","age":10.0},{"name":"yourName2","age":20.0}]');

SET @json = '[{"name":"MyName","age":10.0},{"name":"yourName","age":20.0}]'

✓

SELECT JSON_EXTRACT(@json,'$[0].name')

| JSON_EXTRACT(@json,'$[0].name') |
| :------------------------------ |
| "MyName"                        |

SELECT
  rec_num,
  idx,
  CAST( JSON_UNQUOTE(JSON_EXTRACT(jdoc, CONCAT('$[', idx, '].name'))) as CHAR) AS name
  ,CAST( JSON_UNQUOTE(JSON_EXTRACT(jdoc, CONCAT('$[', idx, '].age'))) AS DECIMAL(3,1) ) * 2 AS age
FROM t1
  -- Inline table of sequential values to index into JSON array
JOIN ( 
  SELECT  0 AS idx UNION
  SELECT  1 AS idx UNION
  SELECT  2 AS idx UNION
  -- ... continue as needed to max length of JSON array
  SELECT  3
  ) AS indexes
WHERE JSON_EXTRACT(jdoc, CONCAT('$[', idx, ']')) IS NOT NULL
ORDER BY rec_num, idx;

rec_num | idx | name      |  age
------: | --: | :-------- | ---:
      1 |   0 | MyName    | 20.2
      1 |   1 | yourName  | 40.0
      2 |   0 | MyName2   | 20.0
      2 |   1 | yourName2 | 40.0

SELECT 
rec_num,json_arrayagg(
    json_merge(
          json_object('name', name), 
          json_object('age', age)
    ) 
) jdoc FROM (
SELECT
  rec_num,
  idx,
  CAST( JSON_UNQUOTE(JSON_EXTRACT(jdoc, CONCAT('$[', idx, '].name'))) as CHAR) AS name
  ,CAST( JSON_UNQUOTE(JSON_EXTRACT(jdoc, CONCAT('$[', idx, '].age'))) AS DECIMAL(3,1) ) * 2 AS age
FROM t1
  -- Inline table of sequential values to index into JSON array
JOIN ( 
  SELECT  0 AS idx UNION
  SELECT  1 AS idx UNION
  SELECT  2 AS idx UNION
  -- ... continue as needed to max length of JSON array
  SELECT  3
  ) AS indexes
WHERE JSON_EXTRACT(jdoc, CONCAT('$[', idx, ']')) IS NOT NULL
ORDER BY rec_num, idx) t
GROUP BY rec_num
;

rec_num | jdoc                                                                  
------: | :---------------------------------------------------------------------
      1 | [{"age": 20.2, "name": "MyName"}, {"age": 40.0, "name": "yourName"}]  
      2 | [{"age": 20.0, "name": "MyName2"}, {"age": 40.0, "name": "yourName2"}]

UPDATE t1
JOIN
(SELECT 
rec_num,json_arrayagg(
    json_merge(
          json_object('name', name), 
          json_object('age', age)
    ) 
) jdoc FROM (
SELECT
  rec_num,
  idx,
  CAST( JSON_UNQUOTE(JSON_EXTRACT(jdoc, CONCAT('$[', idx, '].name'))) as CHAR) AS name
  ,CAST( JSON_UNQUOTE(JSON_EXTRACT(jdoc, CONCAT('$[', idx, '].age'))) AS DECIMAL(3,1) ) * 2 AS age
FROM t1
  -- Inline table of sequential values to index into JSON array
JOIN ( 
  SELECT  0 AS idx UNION
  SELECT  1 AS idx UNION
  SELECT  2 AS idx UNION
  -- ... continue as needed to max length of JSON array
  SELECT  3
  ) AS indexes
WHERE JSON_EXTRACT(jdoc, CONCAT('$[', idx, ']')) IS NOT NULL
ORDER BY rec_num, idx) t
GROUP BY rec_num) t2 USING(rec_num) 
SET t1.jdoc = t2.jdoc

✓

SELECT rec_num,CAST( JSON_UNQUOTE(jdoc) as CHAR) FROM t1

rec_num | CAST( JSON_UNQUOTE(jdoc) as CHAR)                                 
------: | :-----------------------------------------------------------------
      1 | [{"age": 20.2, "name": "MyName"}, {"age": 40, "name": "yourName"}]
      2 | [{"age": 20, "name": "MyName2"}, {"age": 40, "name": "yourName2"}]

db<>fiddle here