如何将JSON解析为行而非列并实现SQL数据表更新？

我明白你现在的问题——你想把JSON里的每个quote键值对拆成数据表的单独一行，而不是把每个quote变成一列对吧？原来的写法是硬编码了每个quote作为列，所以才会生成多列而非多行，咱们换个思路用嵌套的OPENJSON来处理就好啦。

下面是修正后的代码，能直接满足你“每个quote单独一行，quote键作为ABBR列值”的需求：

DECLARE @fx_rates_json NVARCHAR(MAX)
SET @fx_rates_json = N'{ "date": "2019-12-02", "quotes": { "USDARS": 3.673197, "USDAUD": 78.79768, "USDBGN": 110.795056 } }'

SELECT 
    outer_json.[Date],
    quotes_json.[key] AS ABBR,
    CAST(quotes_json.[value] AS FLOAT) AS ExchangeRate
FROM OPENJSON(@fx_rates_json)
WITH (
    [Date] DATE '$.date',
    quotes NVARCHAR(MAX) '$.quotes' AS JSON -- 将quotes作为嵌套JSON提取，方便二次解析
) AS outer_json
CROSS APPLY OPENJSON(outer_json.quotes) AS quotes_json

代码逻辑说明： #

• 第一步解析外层JSON时，我们用AS JSON关键字把quotes字段标记为嵌套JSON类型，而不是直接提取具体值；
• 接着用CROSS APPLY调用OPENJSON解析这个嵌套的quotes对象——OPENJSON解析JSON对象时，默认会返回key（对象的属性名，也就是你的quote键，比如USDARS）、value（对应的汇率数值）、type（值的数据类型）三列；
• 最后把外层的日期、quote键作为ABBR、转换类型后的汇率值组合起来，就得到了每行对应一个quote的结果。

如果你的ABBR字段只需要货币代码的后缀（比如从USDARS中提取ARS），可以把quotes_json.[key] AS ABBR改成RIGHT(quotes_json.[key], 3) AS ABBR，就能自动截取键名的后三位作为ABBR值。

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