如何用TSQL生成以BoardId、StoreId、Code为主键节点的嵌套JSON

能否用FOR JSON原生实现？ #

原生FOR JSON语法默认把列名作为JSON键名，不直接支持将字段值作为动态键，所以没法一行SQL直接生成你要的格式，但可以通过TSQL嵌套查询+字符串拼接的方式实现，也可以选择更易维护的应用层转换方案。

方案1：纯TSQL实现（兼容SQL Server 2016及以上） #

假设你的原始表名为Inventory，可以参考以下代码生成符合要求的JSON：

DECLARE @FirebaseJson NVARCHAR(MAX)

-- 逐层拼接动态键：BoardId -> StoreId -> Code
SELECT @FirebaseJson = COALESCE(@FirebaseJson + ',', '') +
    -- 外层用BoardId值作为键，不需要前导零就转成INT，要保留就直接用BoardId字段
    QUOTENAME(CAST(BoardId AS INT)) + ':' +
    (
        SELECT '{' + STRING_AGG(
            QUOTENAME(StoreId) + ':' +
            (
                SELECT '{' + STRING_AGG(
                    QUOTENAME(Code) + ':' +
                    (
                        -- 内层生成每个Code对应的属性对象
                        SELECT 
                            CAST(BoardId AS INT) AS BoardId,
                            Code,
                            Quantity,
                            StoreId,
                            SYSDATETIMEOFFSET() AS TimeStamp
                        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
                    )
                , ',')
                FROM Inventory i2
                WHERE i2.BoardId = i1.BoardId AND i2.StoreId = i1.StoreId
                GROUP BY BoardId, StoreId
            )
        , ',') + '}'
        FROM Inventory i1
        WHERE i1.BoardId = i.BoardId
        GROUP BY BoardId
    )
FROM Inventory i
GROUP BY BoardId

-- 补全最外层括号生成完整JSON
SET @FirebaseJson = '{' + @FirebaseJson + '}'

-- 输出结果
SELECT @FirebaseJson AS Result

执行后输出的JSON完全匹配你要求的层级结构，可直接用于Firebase的批量更新操作。如果使用的SQL Server版本低于2017不支持STRING_AGG，可以改用FOR XML PATH的方式做字符串拼接。

方案2：应用层转换（更推荐用于同步场景） #

如果你的同步任务是通过代码实现（比如NodeJS、Python、C#），更建议把转换逻辑放在应用层：

• SQL端直接查询所有需要的原始数据，用普通查询或者FOR JSON AUTO返回结构化数组即可
• 代码中遍历数据，按BoardId->StoreId->Code的层级组装对象，最后序列化为JSON

这种方式逻辑更清晰，后续修改格式也更方便，数据量大的时候性能也比SQL拼接更可控。

注意事项 #

• Firebase批量写入单次最多支持500个操作，数据量大的时候可以拆分批次处理
• 如果需要保留BoardId的前导零，把SQL中CAST(BoardId AS INT)的逻辑去掉，直接用原字段值作为键即可
• 时间戳可以根据你的需求调整，SYSDATETIMEOFFSET()会生成带时区偏移的格式，和你示例中的格式完全一致

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