6

in MySQL 5.7 we have the JSON_ARRAY object. I'd like to perform something similar to a SELECT GROUP_CONCAT(field) but with the results into a JSON_ARRAY.

My current query is:

SELECT GROUP_CONCAT(name) FROM users;

result: john,michael,sofia

I'd like the result to be: ["john","michael","sofia"]

My current solution is:

select @j:=json_array_append(@j,'$',name) from users

But that's very inefficient since it's re-calculated for every row. Is it possible to achieve that more efficiently?

Stephen Kennedy
  • 20,585
  • 22
  • 95
  • 108
Stefano Giacone
  • 2,016
  • 3
  • 27
  • 50

2 Answers2

3

You can use JSON_ARRAY to achieve what you want :

SELECT JSON_ARRAY(GROUP_CONCAT(name SEPARATOR ',')) AS names FROM users;

Like this it will let you obtain the desired result without having to re-calculate for each row.

Sampath Wijesinghe
  • 789
  • 1
  • 11
  • 33
MI53RE
  • 313
  • 3
  • 11
-1

You can use JSON_ARRAYAGG Example here:

https://github.com/AndreyMashukov/mysql-json-serializer

Andrey Mashukov
  • 170
  • 7