Way to remove double quotes and convert data type to string in Google BigQuery

Question

The values ​​are of JSON object type and I want to change them to strings and remove the double quote symbols. I tried the replace and trim functions but they require strings as input. I also tried the replace, trim and cast functions but they require the input to be in string format. Select replace(cast([column name]asstring),""")from[table name]

P粉966979765 · Answer

To convert a JSON column to STRING, you can use the JSON_EXTRACT_SCALAR function. Example:

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

Output results: