1

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?

0

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.

  • It doesn't look like it does an implicit distinct. – blindsnowmobile Jun 6 at 18:49
0

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,
                '"'
             )
          ),
          ']'
       ) 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.