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.

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]