这些值是JSON对象类型，我想将它们更改为字符串并删除双引号符号。我尝试了replace和trim函数，但它们需要字符串作为输入。 我还尝试了replace、trim和cast函数，但它们要求输入为字符串格式。 选择replace(cast([列名] as string), """) from [表名]
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
要将
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输出结果：