存储JSON键值对表的复杂JSON验证算法及关联查询需求

1. 可复用的键值对提取方案（规避会话丢失问题） #

由于会话丢失会导致会话级临时表失效，改用持久化中间表存储JSON键值对，确保数据不会随会话结束消失：

-- 创建持久化中间表（仅首次运行）
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'JsonKeyValueStore')
CREATE TABLE dbo.JsonKeyValueStore (
    SourceTableID INT, -- 关联原JSON表主键，用于追溯数据来源
    JsonKey NVARCHAR(255) NOT NULL,
    JsonValue NVARCHAR(MAX) NOT NULL,
    PRIMARY KEY (SourceTableID, JsonKey)
)

-- 清空并重新导入所有JSON键值对（假设原表为JsonData，主键ID，JSON列为ComplexJson）
TRUNCATE TABLE dbo.JsonKeyValueStore;
INSERT INTO dbo.JsonKeyValueStore (SourceTableID, JsonKey, JsonValue)
SELECT 
    jd.ID,
    oj.[key] AS JsonKey,
    oj.[value] AS JsonValue
FROM dbo.JsonData jd
CROSS APPLY OPENJSON(jd.ComplexJson) oj;

为提升后续关联查询效率，可添加索引：

CREATE NONCLUSTERED INDEX IX_JsonKeyValueStore_JsonValue ON dbo.JsonKeyValueStore(JsonValue);

2. 键值对关联父子表的查询 #

假设父子表为ParentChild，其中ParentKey对应JSON中的键，ParentValue对应JSON值，ChildInfo为需提取的子级信息：

指定单个键查询 #

每次针对单个键值对关联查询的语句：

SELECT 
    jks.SourceTableID,
    jks.JsonKey,
    jks.JsonValue AS ParentKeyValue,
    pc.ChildInfo
FROM dbo.JsonKeyValueStore jks
JOIN dbo.ParentChild pc 
    ON jks.JsonKey = pc.ParentKey 
    AND jks.JsonValue = pc.ParentValue
WHERE jks.JsonKey = 'ParentID'; -- 替换为目标键名

批量遍历所有键查询 #

如果无需逐个执行，可一次性获取所有键关联的子级信息：

SELECT 
    jks.SourceTableID,
    jks.JsonKey AS ParentKey,
    jks.JsonValue AS ParentKeyValue,
    pc.ChildInfo
FROM dbo.JsonKeyValueStore jks
LEFT JOIN dbo.ParentChild pc 
    ON jks.JsonKey = pc.ParentKey 
    AND jks.JsonValue = pc.ParentValue
ORDER BY jks.SourceTableID, jks.JsonKey;

3. 预期数据集示例 #

原数据 #

• 原JSON表JsonData：

• 父子表ParentChild：

批量查询结果 #

指定键ParentID查询结果 #

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