Rewrite the title as: Concatenate JSON array to string in MySQL

Question

I'm looking for a way to concatenate JSON arrays into delimited strings in MySQL.

Below is an example using the nested REPLACE() function to replace characters in a string.

SELECT REPLACE(REPLACE(REPLACE(JSON_KEYS('{
  "foo": "I am foo",
  "bar": "I am bar",
  "baz": "I am baz"
}'), '["', ''), '", "', '|'), '"]', '') AS value;

Return results...

bar|baz|foo

Is there a native way to do this without having to do some hacky stuff like this?

Answer 1

You are asking for JSON to CSV conversion using keys instead of values. The real "problem" is using keys, but this can be achieved in a cleaner way. I don't think this way is cleaner, but you might find it more elegant. Just to be extra certain: No, there isn't any native way in MySQL that does what you need.

SELECT 
    GROUP_CONCAT(json_source.a_key SEPARATOR '|') 
FROM JSON_TABLE(
    JSON_KEYS('{
        "foo": "I am foo",
        "bar": "I am bar",
        "baz": "I am baz"
        }'), 
    "$[*]" COLUMNS(a_key TEXT PATH '$')
  ) AS json_source;

JSON_TABLE is a powerful tool, but requires reading and a lot of communication to understand. I myself am still in the communication stage. GROUP_CONCAT works like you would expect, but vertically.

Edit: You can also refer to this Similar question.