如何将结构动态变化的JSON文件导入并扁平化至SQL表？

面对结构动态变化的JSON文件（属性随时增减），可以通过动态探测结构+自动生成SQL的方式实现扁平化，以下是几种主流数据库的具体实现：

1. SQL Server 实现 #

步骤1：提取所有JSON字段（含嵌套） #

先通过OPENJSON解析JSON，提取所有顶级字段和嵌套的sellerDetails字段：

-- 假设JSON内容已存入变量@json
DECLARE @json NVARCHAR(MAX) = N'[
  {"productCode":"00001","productType":"Food","sellerDetails":[{"sellerName":"Cosco","sellerCountry":"UK"}]},
  {"productCode":"00002","productType":"Clothing"},
  {"productCode":"00003","productType":"Toy","sellerDetails":[{"sellerName":"Cosco","sellerCountry":"AUS","sellerState":"VIC"}]}
]';

-- 提取顶级字段
DROP TABLE IF EXISTS #TopLevelFields;
SELECT DISTINCT [key] AS FieldName
INTO #TopLevelFields
FROM OPENJSON(@json)
CROSS APPLY OPENJSON(Value)

-- 提取sellerDetails嵌套字段
DROP TABLE IF EXISTS #NestedFields;
SELECT DISTINCT nd.[key] AS FieldName
INTO #NestedFields
FROM OPENJSON(@json) j
CROSS APPLY OPENJSON(j.Value, '$.sellerDetails') sd
CROSS APPLY OPENJSON(sd.Value) nd;

步骤2：动态生成扁平化SQL #

拼接字段列表，处理嵌套字段的NULL情况，然后执行动态SQL：

DECLARE @selectCols NVARCHAR(MAX), @sql NVARCHAR(MAX);

-- 拼接顶级字段
SELECT @selectCols = STRING_AGG(CONCAT('j.value->''$.', FieldName, ''' AS ', QUOTENAME(FieldName)), ', ')
FROM #TopLevelFields;

-- 拼接嵌套字段（处理无sellerDetails的情况）
SELECT @selectCols = CONCAT(@selectCols, ', ', STRING_AGG(CONCAT('ISNULL(sd.value->''$.', FieldName, ''', ''NULL'') AS ', QUOTENAME(FieldName)), ', '))
FROM #NestedFields;

-- 生成完整SQL
SET @sql = CONCAT(N'
SELECT ', @selectCols, '
FROM OPENJSON(@json) j
OUTER APPLY OPENJSON(j.value, ''$.sellerDetails'') sd;
');

-- 执行动态SQL
EXEC sp_executesql @sql, N'@json NVARCHAR(MAX)', @json = @json;

这段代码会自动识别新增的sellerState字段，无需修改SQL结构。

2. BigQuery 实现 #

BigQuery对动态JSON的支持更简洁，直接用UNNEST展开嵌套数组，SELECT *会自动包含所有字段：

-- 假设JSON已加载到临时表`temp.product_json`的`json_data`列
WITH flattened AS (
  SELECT
    json_data.*,
    seller_detail.*
  FROM `temp.product_json`
  CROSS JOIN UNNEST(json_data.sellerDetails) AS seller_detail
  UNION ALL
  -- 处理无sellerDetails的记录
  SELECT
    json_data.*,
    NULL AS sellerName,
    NULL AS sellerCountry,
    NULL AS sellerState -- 新增字段会自动被识别（如果JSON里有）
  FROM `temp.product_json`
  WHERE ARRAY_LENGTH(json_data.sellerDetails) = 0
)
SELECT * EXCEPT(sellerDetails) FROM flattened;

如果JSON新增了字段，SELECT *会自动包含新字段，无需调整代码。

3. 通用跨数据库方案（Python辅助） #

如果需要适配多种数据库，用Python先解析JSON结构，自动生成建表和插入SQL：

import json
import pandas as pd

# 读取JSON文件
with open('file2.json', 'r') as f:
    data = json.load(f)

# 扁平化JSON（处理嵌套的sellerDetails）
flattened_data = []
for item in data:
    base = {k: v for k, v in item.items() if k != 'sellerDetails'}
    if 'sellerDetails' in item and item['sellerDetails']:
        for seller in item['sellerDetails']:
            flattened_data.append({**base, **seller})
    else:
        flattened_data.append(base)

# 转为DataFrame
df = pd.DataFrame(flattened_data)

# 生成建表SQL
create_table_sql = f"CREATE TABLE products ({', '.join([f'{col} VARCHAR(255)' for col in df.columns])});"
# 生成插入SQL
insert_sql = f"INSERT INTO products ({', '.join(df.columns)}) VALUES {', '.join([str(tuple(row)) for row in df.values])};"

# 执行SQL（用对应的数据库连接，比如psycopg2/pymssql等）

这种方式完全动态，不管JSON新增多少字段，都会自动同步到SQL表结构。

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