Mysql: Insert into JSON column type with GROUP_CONCAT

Question

I'm trying to insert a list of words into a column with the JSON type:

create table my_table (
 ...
 synonyms JSON
 ...
)

My query looks like this:

INSERT into my_table (id, synonyms)
SELECT id, GROUP_CONCAT(DISTINCT synonyms) as synonyms from (
 SELECT id, name1,name2, GROUP_CONCAT(DISTINCT name1) as synonyms
 FROM products
 GROUP BY id
 UNION
 SELECT id, name1,name2, GROUP_CONCAT(DISTINCT name2) as synonyms
 FROM products
 GROUP BY id
) t group by id;

If run without the INSERT in line one, the synonyms column returns a list of comma separated words:

id name1 name2 synonyms
------------------------------------
1 one a,b one,a,b
2 two c two,c

I want to insert those synonyms as a JSON_ARRAY. However, the INSERT fails when the individual words are not double quoted. If I add JSON_ARRAY in my select:

SELECT name1,name2,id, JSON_ARRAY(GROUP_CONCAT(DISTINCT synonyms)) ...

I get the following result:

name1 name2 synonyms
----------------------------
one a,b ["one,a,b"]
two c ["two,c"]

When what I want is this (so it can be inserted directly into the synonyms column):

name1 name2 synonyms
----------------------------
one a,b ["one","a","b"]
two c ["two","c"]

Is there a way to do this with SQL, or is it better handled within application code?

Answer 1

Use JSON_ARRAYAGG() instead of GROUP_CONCAT(). - akina

It is available only in 5.7.22+

Does it have an implicit DISTINCT? I don't know...if not, subquery with DISTINCT will help.

• blindsnowmobile: It doesn't look like it does an implicit distinct.

Answer 2

You can add a concat inside the group_concat to achieve this, like in this sample:

SELECT name1, name2, id, 
 CONCAT(
 '[',
 GROUP_CONCAT(
 CONCAT(
 '"',
 synonyms,
 '"'
 )
 ),
 ']'
 ) 

Answer 3

Query like this

SELECT 
 campaign_id, 
 CAST( CONCAT('[', GROUP_CONCAT(id),']') AS JSON ) AS posts 
FROM 
 posts
WHERE 
 campaign_id IS NOT NULL
GROUP BY campaign_id

will return

campaign_id	posts
4	[1005, 1006, 1007]
5	[1002, 1003, 1004]