MySQL how to select into JSON ARRAY

Question

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?

Sampath Wijesinghe · Accepted Answer · 2020-09-08 13:17:51Z

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.

edited Sep 8, 2020 at 13:17

Sampath Wijesinghe

6901 gold badge11 silver badges34 bronze badges

answered Jun 23, 2018 at 10:50

MI53RE

3133 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL how to select into JSON ARRAY

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related