[SOLVED] Is there a way to merge json arrays in one cell in mysql

Issue

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.

Solution

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

Answered By – nbk

Answer Checked By – Katrina (BugsFixing Volunteer)