SQL Server如何解析嵌套JSON导入Cards、Sets两张表

全程用SQL Server原生OPENJSON函数即可完成两张表的解析导入，不需要额外写循环或第三方脚本。

一、Cards表完整导入实现 #

你原有脚本遗漏了嵌套节点的路径映射，包括禁限信息、卡图、灵摆刻度、连接标记这几个嵌套/数组字段，直接用路径映射+存在性判断就能实现连接标记列的自动填充，修改后的完整代码如下：

Declare @JSON varchar(max)
Declare @JSON2 varchar(max)
-- 读取本地JSON文件
SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK 'C:\Users\User\Desktop\YGOCards.json', SINGLE_CLOB) import
-- 接口返回根节点下的data字段才是卡片数组，直接提取对应值
SELECT @JSON2 = value 
FROM OPENJSON (@JSON) where [key] = 'data'

-- 解析写入Cards临时表
select * into #cards
from openjson(@JSON2)
with (
    [id] nvarchar(4000) '$.id'
    , [name] nvarchar(1000) '$.name'
    , [type] nvarchar(1000) '$.type'
    , [desc] nvarchar(max) '$.desc'
    , [race] nvarchar(1000) '$.race'
    , [archetype] nvarchar(1000) '$.archetype'
    , [atk] nvarchar(1000) '$.atk'
    , [def] nvarchar(1000) '$.def'
    -- 禁限信息在banlist_info嵌套节点下
    , [ban_tcg] nvarchar(1000) '$.banlist_info.ban_tcg'
    , [ban_ocg] nvarchar(1000) '$.banlist_info.ban_ocg'
    , [ban_goat] nvarchar(1000) '$.banlist_info.ban_goat'
    , [level] nvarchar(1000) '$.level'
    , [attribute] nvarchar(1000) '$.attribute'
    , [linkval] nvarchar(1000) '$.linkval'
    -- 卡图信息取card_images数组第一个元素的对应字段
    , [image_url] nvarchar(2000) '$.card_images[0].image_url'
    , [image_url_small] nvarchar(2000) '$.card_images[0].image_url_small'
    , [pendulum_scale] nvarchar(1000) '$.pendulum_scale'
    -- 连接标记列：判断对应方向值是否存在于linkmarkers数组，存在则填方向名，否则返回null
    , [linkmarker_topleft] nvarchar(100) N'$.linkmarkers' as iif(exists(select 1 from openjson(linkmarker_topleft) where value = 'Top-Left'), 'Top-Left', null)
    , [linkmarker_top] nvarchar(100) N'$.linkmarkers' as iif(exists(select 1 from openjson(linkmarker_top) where value = 'Top'), 'Top', null)
    , [linkmarker_topright] nvarchar(100) N'$.linkmarkers' as iif(exists(select 1 from openjson(linkmarker_topright) where value = 'Top-Right'), 'Top-Right', null)
    , [linkmarker_left] nvarchar(100) N'$.linkmarkers' as iif(exists(select 1 from openjson(linkmarker_left) where value = 'Left'), 'Left', null)
    , [linkmarker_right] nvarchar(100) N'$.linkmarkers' as iif(exists(select 1 from openjson(linkmarker_right) where value = 'Right'), 'Right', null)
    , [linkmarker_bottomleft] nvarchar(100) N'$.linkmarkers' as iif(exists(select 1 from openjson(linkmarker_bottomleft) where value = 'Bottom-Left'), 'Bottom-Left', null)
    , [linkmarker_bottom] nvarchar(100) N'$.linkmarkers' as iif(exists(select 1 from openjson(linkmarker_bottom) where value = 'Bottom'), 'Bottom', null)
    , [linkmarker_bottomright] nvarchar(100) N'$.linkmarkers' as iif(exists(select 1 from openjson(linkmarker_bottomright) where value = 'Bottom-Right'), 'Bottom-Right', null)
)

注意：接口返回的斜向连接标记值带横杠（如Top-Left、Bottom-Right），匹配时不要写错值；非连接卡的linkmarkers字段为null，判断逻辑会自动给所有连接标记列填null，不需要额外处理。你提到的id为94259633的卡，执行后linkmarker_top列值为Top，其余连接标记列全为null，完全符合预期。

二、Sets表导入实现 #

单张卡对应多个卡包信息，属于一对多关系，卡包数据存在每张卡的card_sets数组里，用CROSS APPLY拆分数组即可关联卡片id，代码如下：

select * into #sets
from openjson(@JSON2)
with (
    [id] nvarchar(4000) '$.id',
    [card_sets] nvarchar(max) '$.card_sets' as json
)
-- 拆分每个卡的卡包数组为多行
cross apply openjson(card_sets)
with (
    [set_name] nvarchar(1000) '$.set_name',
    [set_code] nvarchar(1000) '$.set_code',
    [set_rarity] nvarchar(1000) '$.set_rarity',
    [set_rarity_code] nvarchar(1000) '$.set_rarity_code'
)

你提到的id为34541863的首条记录，执行后返回的行完全匹配预期：

注意事项 #

• 执行前把脚本里的JSON文件路径替换为你本地的实际存放路径
• desc字段为卡片效果文本，长度普遍超过1000字符，脚本里已经改成nvarchar(max)避免内容截断
• 该写法仅支持SQL Server 2016及以上版本，低版本没有内置OPENJSON函数无法运行

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