SQL Server存储嵌套JSON的单表层级模型设计合理性咨询

原表设计合理性评估 #

你当前采用的邻接表设计思路是层级数据存储的经典实现，逻辑通顺，但存在几个核心缺陷无法落地实际使用：

• 缺少实际值存储字段：你用IsValue区分了值节点，但没有对应字段存储JSON节点的实际取值，无法承载业务数据
• 缺少根节点关联标识：如果需要存储多份不同的嵌套JSON记录，没有字段区分不同JSON的根节点，会导致多份数据的层级关系混乱
• 缺少节点类型标识：无法区分数组节点、对象节点、字符串/数值/布尔等基础类型节点，还原JSON时会出现结构错误

单表存储嵌套JSON层级结构优化方案 #

调整后的可落地表结构如下：

USE [NFC]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tbl_NFCInquiryStructure](
    [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [RootId] [int] NOT NULL, -- 同一份JSON的所有节点共用同一个根节点ID，用于区分不同的JSON记录
    [NodeName] [nvarchar](500) NULL, -- JSON节点的键名，数组元素节点可存储索引值或留空
    [ParentId] [int] NULL, -- 父节点ID，根节点该字段为0
    [NodeType] [nvarchar](20) NOT NULL, -- 取值范围：object/array/string/number/boolean/null
    [NodeValue] [nvarchar](max) NULL, -- 存储值节点的实际取值，对象/数组节点该字段留空
    [SortOrder] [int] NOT NULL DEFAULT 0 -- 同父级下的节点排序，主要用于保留数组元素的原有顺序
) ON [PRIMARY]
GO

方案说明 #

• 单份JSON的所有节点通过RootId绑定，查询时指定RootId即可取出整份JSON的完整结构，满足你「嵌套JSON作为单条记录存储」的核心需求
• NodeType字段明确节点类型，还原JSON时可正确生成对应格式，不会出现对象转数组、布尔值变字符串这类格式错误
• SortOrder字段保留数组元素的顺序，避免JSON还原后数组排序错乱
• 如果你使用的SQL Server版本为2016及以上，也可以直接用NVARCHAR(MAX)字段存储完整JSON，搭配SQL Server内置的JSON函数做查询解析，性能远高于邻接表方案，适合不需要频繁修改单个JSON节点的场景

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