BigQuery：从json对象中提取键，将json从对象转换为键值数组

从JSON对象中提取键：BigQuery中的键值数组转换

json
{
  "id": 1,
  "name": "John Doe",
  "address": {
    "city": "New York",
    "state": "NY"
  }
}

sql
SELECT
  JSON_EXTRACT(json_column, '$.id') AS id,
  JSON_EXTRACT(json_column, '$.name') AS name
FROM
  your_table

sql
WITH json_data AS (
  SELECT
    '{"id": 1, "name": "John Doe", "address": {"city": "New York", "state": "NY"}}' AS json_str
  UNION ALL
  SELECT
    '{"id": 2, "name": "Jane Smith", "address": {"city": "Los Angeles", "state": "CA"}}' AS json_str
  -- Add more JSON data if needed
)
SELECT
  JSON_EXTRACT(json_str, '$.id') AS id,
  JSON_EXTRACT(json_str, '$.name') AS name,
  JSON_EXTRACT(json_str, '$.address.city') AS city,
  JSON_EXTRACT(json_str, '$.address.state') AS state
FROM
  json_data