MySQL 5.7 Concatenate values of JSON string into one string

Question

I have a table where the content column consists of a JSON object, with an example value:

{"blocks":[{"score":"A"},{"score":"A"},{"score":"B"}]}

What I am trying to do is SELECT for a string representation of all the scores, so for this example I want:

AAB

I have been able to parse the JSON easily:

SELECT json_extract(content, '$.blocks[*].score') AS scores

Which results in:

["A", "A", "B"]

but for some reason I'm having an issue concatenating that JSON array into a single string (aside from casting it as a string and calling a few replace functions to remove the quotes and brackets). I've tried variations of CONCAT and GROUP_CONCAT but have not found a solution.

What is the proper way to concatinate this JSON string into a single string?

Answer 1

You can use a catalog table such as information_schema.tables in order to generate rows to iterate by the length of the array, and then aggregate all members by using GROUP_CONCAT() such as

SELECT GROUP_CONCAT(
                    JSON_UNQUOTE(
                       JSON_EXTRACT(content,
                                    CONCAT('$.blocks[', i - 1, '].score'))
                                   ) 
                       SEPARATOR '') AS scores
  FROM (SELECT JSON_LENGTH(JSON_EXTRACT(content, '$.blocks[*].score')) AS len,
               @i := @i + 1 AS i,
               content
          FROM tab
          JOIN information_schema.tables
          JOIN (SELECT @i := 0) AS i) AS t
 WHERE i <= len;

+--------+
| scores |
+--------+
|  AAB   |
+--------+

Demo