<p>这些值是JSON对象类型，我想将它们更改为字符串并删除双引号符号。我尝试了replace和trim函数，但它们需要字符串作为输入。<br />我还尝试了replace、trim和cast函数，但它们要求输入为字符串格式。<br />选择replace(cast([列名] as string), """) from [表名]</p>
WITH sample AS (
SELECT JSON '[{"day_of_week": "SUNDAY", "from": "06:00:00", "to": "15:00:00"}]' as json_data
)
SELECT
JSON_EXTRACT_SCALAR(j.day_of_week) as day_of_week,
JSON_EXTRACT_SCALAR(j.from) as `from`,
JSON_EXTRACT_SCALAR(j.to) as `to`
FROM sample, UNNEST(JSON_EXTRACT_ARRAY(sample.json_data)) j
1
742
![ThinkPHP5快速开发企业站点[全程实录]](https://img.php.cn/upload/course/000/000/068/6253d918a3ce7278.png)
收藏
要将
JSON列转换为STRING，您可以使用JSON_EXTRACT_SCALAR函数。示例：WITH sample AS ( SELECT JSON '[{"day_of_week": "SUNDAY", "from": "06:00:00", "to": "15:00:00"}]' as json_data ) SELECT JSON_EXTRACT_SCALAR(j.day_of_week) as day_of_week, JSON_EXTRACT_SCALAR(j.from) as `from`, JSON_EXTRACT_SCALAR(j.to) as `to` FROM sample, UNNEST(JSON_EXTRACT_ARRAY(sample.json_data)) j输出结果：