Is there a way to merge json arrays in one cell in mysql. I have a table structure which I'd not be able to modify like below:

select group_concat(Distinct left SEPARATOR ',') 
from temptbl where json_contains(right ,'"ba"','$') ;

which returns

[11, 12, 13, 4],[411, 12, 13, 4]

in a cell and I'd like to merge those values into one array. Any advice is appreciated.

CodePudding user response：

It is not pretty, but it works just fine, but you canuse JSON_MeRGE in a dynamic sql to achieve that

CREATE TABLE temptbl (
  `left` JSON,
  `right` JSON
);

INSERT INTO temptbl
  (`left`, `right`)
VALUES
  ('[1, 2, 3, 4]', '["a", "ba"]'),
  ('[10, 20, 30, 40]', '["a", "ba"]');

select group_concat(Distinct CONCAT("'",`left`,"'") SEPARATOR ',') INTO @sql
from temptbl where json_contains(`right` ,'"ba"','$') ;

SELECT CONCAT("SELECT JSON_MERGE( ",@sql,");") INTO @sql;
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

| JSON_MERGE( '[1, 2, 3, 4]','[10, 20, 30, 40]') | | :--------------------------------------------- | | [1, 2, 3, 4, 10, 20, 30, 40] |

db<>fiddle here

CodePudding user response：

MySQL has a limited number of builtin JSON functions. You need something like JSON_ARRAY_APPEND() but which appends JSON arrays in an aggregated manner like JSON_ARRAYAGG(). I don't think there is any JSON function in MySQL that will do what you describe.

I recommend the simpler solution is not to try to append all the arrays together in SQL. Just fetch all the individual arrays to your client application, and then write code to loop over the result set and append the JSON strings into an array in your code.

Another solution would be to not store arrays in JSON at all, but store one value per row, and the arrays would become sets of rows. Then GROUP_CONCAT() would work more simply. But you said you cannot change the structure of your database.

That's a pity, because storing data in JSON format and then trying to use SQL expressions to search or manipulate the JSON as if it is relational data is frequently more complex.