Rewrite the title as: Concatenate JSON array to string in MySQL-PHP Chinese Network Q&A

Article Topic Learning Download Q&A Programming Dictionary Game Recent Updates

简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)

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

P粉935883292 2023-09-06 14:59:38

620

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

Below is an example using the nestedREPLACE()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?

P粉935883292

reply all (1)

P粉9523651432023-09-07 12:10:40 1 floor

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_TABLEis a powerful tool, but requires reading and a lot of communication to understand. I myself am still in the communication stage.GROUP_CONCATworks like you would expect, but vertically.

Edit: You can also refer to thisSimilar question.