如何在SQL中转换嵌套JSON结构以适配API请求格式？

存储过程实现 #

以下存储过程通过递归函数处理嵌套的QueryBuilder JSON结构，完成所有要求的格式转换：

CREATE PROCEDURE dbo.ConvertQueryBuilderJSON
    @InputJSON NVARCHAR(MAX),
    @OutputJSON NVARCHAR(MAX) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    -- 递归函数：转换单个规则节点（支持嵌套组/条件节点）
    CREATE OR ALTER FUNCTION dbo.ConvertRuleNode(@NodeJSON NVARCHAR(MAX))
    RETURNS NVARCHAR(MAX)
    AS
    BEGIN
        DECLARE @ConvertedNode NVARCHAR(MAX);
        DECLARE @Condition NVARCHAR(50);
        DECLARE @Rules NVARCHAR(MAX);
        DECLARE @Field NVARCHAR(100);
        DECLARE @Operator NVARCHAR(10);
        DECLARE @Value SQL_VARIANT;

        -- 判断当前节点是组节点（含rules）还是条件节点
        IF JSON_VALUE(@NodeJSON, '$.rules') IS NOT NULL
        BEGIN
            -- 处理组节点：替换condition为querycondition，递归处理子rules
            SET @Condition = JSON_VALUE(@NodeJSON, '$.condition');
            SET @Rules = (
                SELECT dbo.ConvertRuleNode(value) AS [*]
                FROM OPENJSON(@NodeJSON, '$.rules')
                FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
            );
            SET @ConvertedNode = JSON_MODIFY(
                JSON_MODIFY('{}', '$.querycondition', @Condition),
                '$.queryrules', JSON_QUERY(@Rules)
            );
        END
        ELSE
        BEGIN
            -- 处理条件节点：替换field、转换operator
            SET @Field = JSON_VALUE(@NodeJSON, '$.field');
            SET @Operator = JSON_VALUE(@NodeJSON, '$.operator');
            SET @Value = JSON_VALUE(@NodeJSON, '$.value');

            -- 从FIELDDATA表关联获取queryparametername
            DECLARE @QueryParameterName NVARCHAR(100);
            SELECT @QueryParameterName = queryparametername
            FROM FIELDDATA
            WHERE fieldname = @Field;

            -- 转换operator为operatorType
            DECLARE @OperatorType NVARCHAR(20);
            SET @OperatorType = CASE @Operator
                WHEN '=' THEN 'Equals'
                WHEN '!=' THEN 'notEquals'
                -- 可扩展添加其他operator映射规则
                ELSE @Operator
            END;

            -- 构建转换后的条件节点
            SET @ConvertedNode = JSON_MODIFY(
                JSON_MODIFY(
                    JSON_MODIFY('{}', '$.queryparametername', @QueryParameterName),
                    '$.operatorType', @OperatorType
                ),
                '$.value', @Value
            );
        END

        RETURN @ConvertedNode;
    END;

    -- 调用递归函数处理根节点
    SET @OutputJSON = dbo.ConvertRuleNode(@InputJSON);
END

使用示例 #

-- 模拟输入JSON
DECLARE @Input NVARCHAR(MAX) = '{
    "condition": "AND",
    "rules": [
        {
            "field": "username",
            "operator": "=",
            "value": "admin"
        },
        {
            "condition": "OR",
            "rules": [
                {
                    "field": "age",
                    "operator": "!=",
                    "value": 30
                },
                {
                    "field": "status",
                    "operator": "=",
                    "value": "active"
                }
            ]
        }
    ]
}';
DECLARE @Output NVARCHAR(MAX);

-- 执行存储过程
EXEC dbo.ConvertQueryBuilderJSON @InputJSON = @Input, @OutputJSON = @Output OUTPUT;

-- 查看转换结果
SELECT @Output AS ConvertedJSON;

关键说明 #

• 递归处理嵌套结构：通过自定义递归函数ConvertRuleNode遍历所有层级的节点，不管嵌套多少层都能正确转换。
• 字段关联替换：直接在条件节点处理时关联FIELDDATA表，替换原field值为对应的queryparametername。
• Operator映射：通过CASE语句实现=→Equals、!=→notEquals的转换，可根据业务需求扩展其他Operator的映射规则。
• 性能与限制：默认递归深度为100，若业务场景嵌套层级超过此限制，可在调用时添加OPTION (MAXRECURSION 0)（需谨慎使用，避免无限递归）。

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