在Synapse笔记本中，如何用参数表值替换目标表中的参数

场景说明 #

你需要从处理后的CSV文件生成JSON，但CSV对应的SourceTable（原表1）中Parameters1、Parameters2列包含{ParameterName}格式的占位参数，需从ParameterTable（参数表）中获取对应值替换后，再生成最终JSON。

原表结构 #

SourceTable（表1）：

ParameterTable（参数表）：

解决方案 #

方法1：手动嵌套REPLACE（适合参数较少的场景） #

如果参数数量不多，直接用REPLACE函数逐个替换占位符：

SELECT 
    st.Id,
    st.data1,
    -- 替换Parameters1中的两个参数
    REPLACE(REPLACE(st.Parameters1, '{MinimumNumber}', pt_min.Value), '{Time}', pt_time.Value) AS Parameters1,
    -- 替换Parameters2中的Hours参数
    REPLACE(st.Parameters2, '{Hours}', pt_hours.Value) AS Parameters2
FROM SourceTable st
-- 关联参数表获取对应参数值
CROSS JOIN (SELECT Value FROM ParameterTable WHERE ParameterName = 'MinimumNumber') pt_min
CROSS JOIN (SELECT Value FROM ParameterTable WHERE ParameterName = 'Time') pt_time
CROSS JOIN (SELECT Value FROM ParameterTable WHERE ParameterName = 'Hours') pt_hours

方法2：递归CTE替换（适合参数较多的场景） #

如果参数数量不确定或较多，用递归CTE自动遍历参数表完成替换，无需手动修改代码：

WITH ParameterCTE AS (
    -- 给参数表添加行号，用于递归遍历
    SELECT 
        ParameterName,
        Value,
        ROW_NUMBER() OVER (ORDER BY ParameterName) AS RowNum
    FROM ParameterTable
),
RecursiveReplace AS (
    -- 初始步骤：获取原始数据，标记当前替换次数为1
    SELECT 
        Id,
        data1,
        Parameters1,
        Parameters2,
        1 AS CurrentRow
    FROM SourceTable
    UNION ALL
    -- 递归步骤：每次替换一个参数，替换次数+1
    SELECT 
        rr.Id,
        rr.data1,
        REPLACE(rr.Parameters1, CONCAT('{', pc.ParameterName, '}'), pc.Value) AS Parameters1,
        REPLACE(rr.Parameters2, CONCAT('{', pc.ParameterName, '}'), pc.Value) AS Parameters2,
        rr.CurrentRow + 1 AS CurrentRow
    FROM RecursiveReplace rr
    JOIN ParameterCTE pc ON rr.CurrentRow = pc.RowNum
)
-- 获取最后一次替换完成的结果（所有参数都替换完毕）
SELECT 
    Id,
    data1,
    Parameters1,
    Parameters2
FROM RecursiveReplace
WHERE CurrentRow = (SELECT MAX(RowNum) FROM ParameterCTE)

生成最终JSON #

根据Synapse使用的SQL类型选择对应方法：

1. Synapse SQL池（专用/服务器less） #

使用FOR JSON PATH生成JSON：

-- 先将替换结果存入临时表
SELECT 
    Id,
    data1,
    Parameters1,
    Parameters2
INTO #ReplacedTable
FROM RecursiveReplace
WHERE CurrentRow = (SELECT MAX(RowNum) FROM ParameterCTE);

-- 生成带根节点的JSON
SELECT * FROM #ReplacedTable FOR JSON PATH, ROOT('data');

2. Spark SQL（Synapse Spark笔记本） #

使用to_json函数将每行数据转为JSON：

SELECT to_json(struct(*)) AS json_output
FROM (
    -- 嵌入替换后的查询结果
    SELECT 
        Id,
        data1,
        Parameters1,
        Parameters2
    FROM RecursiveReplace
    WHERE CurrentRow = (SELECT MAX(RowNum) FROM ParameterCTE)
) replaced_data;

注意事项 #

• 确保参数表中的ParameterName与原表占位符{ParameterName}中的名称完全一致，否则替换会失效。
• 如果占位符或参数值包含特殊字符（如{、}），需提前处理转义逻辑。

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