如何用OPENJSON提取JSON中动态键名的ID值？

你的问题出在获取动态ID的方式上，OPENJSON解析外层JSON对象时，会直接返回key列，这个列就是你要的动态ID（比如示例里的12345），不需要从value里去解析。

正确的SQL语句 #

DECLARE @json NVARCHAR(MAX) = N'{
    "12345": {
        "percent": 26,
        "avgPrice": 32,
        "avgNewOfferCount": 12,
        "avgUsedOfferCount": -1,
        "isFBA": true,
        "lastSeen": 653
    }
}';

INSERT INTO [test].[test]
SELECT      
    j.[key] AS dynamic_id, -- 直接获取动态ID
    JSON_VALUE(j.[value], '$.percent') AS stat,
    JSON_VALUE(j.[value], '$.avgPrice') AS avg_price,
    JSON_VALUE(j.[value], '$.avgNewOfferCount') AS avg_new_offer_count,
    JSON_VALUE(j.[value], '$.isFBA') AS is_fba
FROM OPENJSON(@json) j;

原理说明 #

当使用OPENJSON(@json)解析外层JSON对象时，它会默认返回三列：

• key：外层对象的键（也就是你的动态ID）
• value：对应键的JSON内容（即内部嵌套的对象）
• type：值的类型标识（比如对象类型为5，数字类型为2）

你之前用JSON_VALUE(j.[value],$.j)是错误的，因为j.[value]是内部的JSON对象，里面根本不存在j这个键，所以返回NULL。直接引用j.[key]就能拿到动态ID。

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