BigQuery未知字段名的JSON字符串转动态列实现方案问询

Absolutely, you can solve this using BigQuery SQL—no JavaScript required! The key is to use dynamic SQL to handle unknown field names, combined with JSON functions to unpack both top-level and nested stringified JSON values.

Here's a step-by-step solution tailored to your sample data:

Step 1: Understand the Data Structure #

Your JSON has a mix of top-level fields (additional_info, stock_level) and nested stringified JSON (sku_types). We need to unpack both to get flat columns like sku_types.id and sku_types.product_code.

Step 2: Full SQL Query #

This query will automatically detect all unique fields (including nested ones) and pivot them into separate columns:

WITH `my_table` AS (
 SELECT '{"sku_types":"{\"id\":\"5433306\",\"product_code\":\"adfklj_ewkj\"}","additional_info":"Face 30 ml","stock_level":"20+"}' as json_string union all
 SELECT '{"additional_info":"Face 100 ml","offer_info":"30%"}' as json_string
)

DECLARE keys ARRAY<STRING>;

-- Collect all distinct keys (including nested ones like sku_types.id)
SET keys = ARRAY(
  SELECT DISTINCT full_key
  FROM (
    -- Extract nested keys from stringified JSON fields
    SELECT
      CONCAT(top_key, '.', nested_key) AS full_key
    FROM (
      SELECT
        key AS top_key,
        JSON_EXTRACT_SCALAR(JSON_PARSE(json_string), CONCAT('$.', key)) AS top_value
      FROM my_table,
      UNNEST(JSON_KEYS(JSON_PARSE(json_string))) AS key
    ) top_level,
    UNNEST(IF(JSON_VALID(top_value), JSON_KEYS(JSON_PARSE(top_value)), [])) AS nested_key
    UNION ALL
    -- Extract top-level keys that aren't stringified JSON
    SELECT key AS full_key
    FROM my_table,
    UNNEST(JSON_KEYS(JSON_PARSE(json_string))) AS key
    WHERE NOT JSON_VALID(JSON_EXTRACT_SCALAR(JSON_PARSE(json_string), CONCAT('$.', key)))
  )
);

-- Dynamically build and run the pivot query
EXECUTE IMMEDIATE FORMAT("""
WITH parsed_data AS (
  -- Unpack nested stringified JSON fields into key-value pairs
  SELECT
    json_string,
    CONCAT(top_key, '.', nested_key) AS full_key,
    JSON_EXTRACT_SCALAR(JSON_PARSE(top_value), CONCAT('$.', nested_key)) AS full_value
  FROM (
    SELECT
      json_string,
      key AS top_key,
      JSON_EXTRACT_SCALAR(JSON_PARSE(json_string), CONCAT('$.', key)) AS top_value
    FROM my_table,
    UNNEST(JSON_KEYS(JSON_PARSE(json_string))) AS key
  ) top_level,
  UNNEST(IF(JSON_VALID(top_value), JSON_KEYS(JSON_PARSE(top_value)), [])) AS nested_key
  UNION ALL
  -- Get top-level key-value pairs that aren't nested JSON
  SELECT
    json_string,
    key AS full_key,
    JSON_EXTRACT_SCALAR(JSON_PARSE(json_string), CONCAT('$.', key)) AS full_value
  FROM my_table,
  UNNEST(JSON_KEYS(JSON_PARSE(json_string))) AS key
  WHERE NOT JSON_VALID(JSON_EXTRACT_SCALAR(JSON_PARSE(json_string), CONCAT('$.', key)))
)
SELECT *
FROM parsed_data
PIVOT (
  MAX(full_value) FOR full_key IN (%s)
)
""", STRING_AGG(FORMAT("'%s'", full_key), ', ') FROM UNNEST(keys) AS full_key);

How It Works #

1. Collect All Keys: First, we scan all rows to identify every unique field (including nested ones like sku_types.id). We use JSON_KEYS to get top-level keys, then check if any values are valid JSON strings—if so, we extract their nested keys and prefix them with the parent field name.
2. Dynamic Pivot: Using EXECUTE IMMEDIATE, we build a pivot query that turns each unique key into a column. The MAX(full_value) ensures we get the single value for each key per row (since each row will have at most one value for any key).
3. Handle Missing Fields: Rows that don't have a particular field will show NULL in that column, which is the expected behavior.

Output #

Running this query will give you the following result:

内容的提问来源于stack exchange，提问作者Giedrius