Mysql: Insert into JSON column type with GROUP_CONCAT 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?
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.