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?
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.
You can use
JSON_ARRAYAGG
Example here:
https://github.com/AndreyMashukov/mysql-json-serializer
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With