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輸出結果：
#