How to convert a JSON array to comma separated string in MySQL?

Question

I've a JSON column from where I'm extracting values. All goes good, except I'm not able to convert an array inside the JSON Object to a comma separated string. Below is the structure similar to what I have.

id	name	data
...	...	...
234	AAA	{ "..." "options": [ "Option A", "Option B", "Option C" ], "..." }
...	...	...

Now I'm extracting the data like this:

SELECT
    `id`,
    `name`,
    JSON_UNQUOTE( JSON_EXTRACT( `data`, '$.options' ) ) AS `options`
FROM `my_table`;

Result I'm getting:

id	name	data
...	...	...
234	AAA	["Option A", "Option B", "Option C"]
...	...	...

Result I want:

id	name	data
...	...	...
234	AAA	"Option A", "Option B", "Option C"
...	...	...

How can I do this in MySQL? It's like implode function in php.

I've seen some answers that uses nested REPLACE but remember that the value itself may contain square brackets.

Which version of mysql you are using

Akhilesh Mishra
– Akhilesh Mishra

2021-05-20 13:19:45 +00:00
Commented May 20, 2021 at 13:19 — Akhilesh Mishra
– Akhilesh Mishra, Commented May 20, 2021 at 13:19
@AkhileshMishra I'm using MySQL v8.0.25.

Sukanta Paul
– Sukanta Paul

2021-05-20 14:06:03 +00:00
Commented May 20, 2021 at 14:06 — Sukanta Paul
– Sukanta Paul, Commented May 20, 2021 at 14:06

marc_s · Accepted Answer · 2021-05-23 20:12:27Z

1

Assuming you are using mysql 8.0 then you can use JSON_TABLE with CROSS JOIN and GROUP_CONCAT to achieve this:

Try below query:

select t1.id,t1.name, group_concat(t2.data_ )
from test t1 
cross join 
json_table(t1.data->'$.options', '$[*]' columns(data_ varchar(10) path '$'))t2
group by 1,2

If you want to surround all the values in quotes then replace group_concat(t2.data_ ) with group_concat(concat('"',t2.data_,'"' )) in above query

Explanation

In your question you want to get the values of options array of the JSON in to comma separated string. So the steps of the solution is :

Step 1. Get the array data from JSON: Here by using -> operator we are able to fetch the data from data field. data->'$.options' will return the array in JSON format.

Step 2. Unnest all the elements of the array: Here JSON_TABLE(Reference) will do the things for us. see below example

select * from json_table(
'["Option A","Option B","Option C"]', 
'$[*]' columns(data_ varchar(10) path '$'))t

Result:

data_
Option A
Option B
Option C

Step 3. Cross join the unnest records: After unnest the array CROSS JOIN it with main table, which will return one row against each records returns by JSON_TABLE. In below example you will get 3 rows against id 234 because there are 3 elements in array.

select t1.id,t1.name,t2.data_
from test t1 
cross join 
json_table(t1.data->'$.options', '$[*]' columns(data_ varchar(10) path '$'))t2

Result:

id    name      data_
234   AAA       Option A
234   AAA       Option B
234   AAA       Option C

Step 4. Group the results: In final step use GROUP_CONCAT(Reference) to group all the records by id, name etc. to get the desired output.

DEMO

edited May 23, 2021 at 20:12

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered May 20, 2021 at 13:46

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

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

3 Comments

Sukanta Paul Over a year ago

Thanks for the answer. Sorry I put quotes in the value, I actually wanted without qoutes. Sorry for asking a bit more, can you please explain how is this working?

Sukanta Paul Over a year ago

Beautiful explaination @AkhileshMishra .. Extremely helphull. Thanks.

MOHIT AGARWAL Over a year ago

group_concat is deprecated in my sql latest version

Collectives™ on Stack Overflow

How to convert a JSON array to comma separated string in MySQL?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related