Question 217359 in Mysql Question posted in
The official MySQL documentation can be found here.

Merge JSON array values across multiple rows in MySQL

UserID Access
1 ["1"]
2 ["2", "3"]
3 ["3"]

I have the above table, I’m looking to return a merged array of all the Access values. So the result would be

["1","2","3"]

I have tried with:
SELECT JSON_ARRAYAGG(Access) FROM table;

But this results in a concatenated JSON array of arrays like:
[["1"],["2","3"],["3"]]

What is the best way of achieving the desired output?

Tags:

2

Answers


  1. 0

    TABLE

    | id | json_array_column   |
| 1  | ["apple", "banana"] |
| 2  | ["orange", "grape"] |
| 3  | ["pear", "kiwi"]    |

    Query

        SELECT JSON_ARRAYAGG(item) AS merged_array
FROM (
  SELECT JSON_UNQUOTE(JSON_EXTRACT(json_array_column, CONCAT('$[', numbers.n, ']'))) AS item
  FROM my_table
  CROSS JOIN (
    SELECT 0 AS n UNION ALL
    SELECT 1 UNION ALL
    SELECT 2 -- Add more numbers here depending on the maximum length of your arrays
  ) AS numbers
) AS items;

    output

    | merged_array |
| ["apple", "banana", "orange", "grape", "pear", "kiwi"] |
    Login or Signup to reply.
  2. 0

    I am going to answer this showing a PHP variant per my comment .. This is just meant to be helpful and possibly useful as an alternative to a complicated SQL call.

    Say you had this table:

    UserID Access
    1 ["1"]
    2 ["2", "3"]
    3 ["3"]
    4 ["2", "4","7"]
    5 ["1", "5","7"]
    6 ["2", "6"]

    You can use this little bit of PHP to give you exactly what you’re looking for using 7 lines of code (provided you make your SQL call).

    $rows = $getDB->makeQuery("SELECT Access FROM your_table");

$merged_array = array();

foreach ($rows as $row){
    $json = json_decode( $row['Access'] );
    $merged_array = array_unique(array_merge($json, $merged_array));
}
sort($merged_array);
print_r( $merged_array );
$new_json = json_encode($merged_array);
echo $new_json;

    That will give you a JSON string that looks like:

    ["1","2","3","4","5","6","7"]

    Every language is different, but rest assured they all have the basic logic I have laid out here ..

    1. Create a empty array
    2. Loop through each SQL row
    3. Convert JSON to array
    4. Merge temp array with current row item
    5. Rid yourself of duplicates
    6. (Loop complete) sort the array (smallest to largest)
    7. Convert array back to JSON
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search