英文:
Concatenate JSON array to a string in MySQL
问题
我正在寻找一种方法将MySQL中的JSON数组连接成一个分隔的字符串。
这是一个示例，使用嵌套的REPLACE()函数来替换字符串中的字符。
SELECT REPLACE(REPLACE(REPLACE(JSON_KEYS('{
"foo": "I am foo",
"bar": "I am bar",
"baz": "I am baz"
}'), '[\"', ''), '\"', '\"', '|'), '\"]', '') AS value;
返回结果...
bar|baz|foo
是否有一种不涉及如此巧妙的方法来实现这个目标？
英文:
I am looking for a way to concatenate a JSON array in MySQL to a delimited string.
Here is an example that uses nested REPLACE() functions to replace characters in the string.
SELECT REPLACE(REPLACE(REPLACE(JSON_KEYS('{
"foo": "I am foo",
"bar": "I am bar",
"baz": "I am baz"
}'), '["', ''), '", "', '|'), '"]', '') AS value;
returns...
bar|baz|foo
Is there a native way to do this that does not involve doing something quite as hacky?
答案1
得分: 2
你正在要求将JSON转换为CSV，而不是使用值而是使用键。真正的“问题”在于使用键，但可以以不太巧妙的方式实现。我认为这种方法可能并不比之前的方法差，但你可能会觉得它更加优雅。只是为了确保：MySQL中没有原生支持你所需的功能。
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 是一个强大的工具，但需要大量阅读和理解。我自己正在学习中。GROUP_CONCAT 的作用与你期望的一样，但是纵向工作。
编辑：还可以参考这个类似的问题。
英文:
You are asking for a JSON to CSV transformation using the keys rather than the values. The real "problem" is the usage of the keys, but it's achievable in a less hacky way. I don't think this is any less hacky, but you might find it more elegant. And just to be extra sure: No, there isn't anything in MySQL that natively 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 potent tool, but it takes a fair bit of reading and a lot of pillow talk to understand. I'm in the pillow talk phase myself. GROUP_CONCAT works as you'd expect, but vertically.
EDIT: Also read for reference this somewhat similar question.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论