如何在Azure Data Factory中动态展开嵌套对象并实现行转列？

针对你遇到的RuleManifestsProperty包含动态命名、结构一致的对象，需要让每个objectClassId对应单个对象的场景，常规UNPIVOT没法直接适配动态对象名，得用JSON解析+动态SQL（如果是JSON存储）或者动态列匹配Unpivot（如果是结构化列）来实现，具体方案如下：

场景1：RuleManifestsProperty是JSON类型字段 #

假设你用的是SQL Server，步骤如下：

1. 提取所有动态对象名
   先从JSON里取出所有顶级键（也就是你说的1、Latitude、pumpe这些对象名称）：

   SELECT DISTINCT [key] AS ObjectName
   FROM YourTable
   CROSS APPLY OPENJSON(YourTable.RuleManifestsProperty)
   

2. 生成并执行动态SQL
   用提取到的对象名，自动拼接解析每个对象属性的SQL，实现行转列：

   DECLARE @ObjectNameList NVARCHAR(MAX)
   DECLARE @DynamicSQL NVARCHAR(MAX)
   
   -- 拼接每个对象的解析逻辑
   SELECT @ObjectNameList = STRING_AGG(
       CONCAT(
           'JSON_VALUE(RuleManifestsProperty, ''$."', [key], '".propertyAlias'') AS propertyAlias,',
           'JSON_VALUE(RuleManifestsProperty, ''$."', [key], '".propertyModelDtid'') AS propertyModelDtid,',
           'JSON_VALUE(RuleManifestsProperty, ''$."', [key], '".propertyModelLegacy'') AS propertyModelLegacy,',
           'JSON_VALUE(RuleManifestsProperty, ''$."', [key], '".propertyType'') AS propertyType,',
           'JSON_VALUE(RuleManifestsProperty, ''$."', [key], '".regexExtension'') AS regexExtension,',
           '''', [key], ''' AS ObjectName'
       ),
       ' UNION ALL SELECT objectClassId, '
   )
   FROM (
       SELECT DISTINCT [key]
       FROM YourTable
       CROSS APPLY OPENJSON(YourTable.RuleManifestsProperty)
   ) AS ObjectNames
   
   -- 组合完整SQL并执行
   SET @DynamicSQL = 'SELECT objectClassId, ' + @ObjectNameList + ' FROM YourTable'
   EXEC sp_executesql @DynamicSQL
   

   这段代码会自动识别所有对象名，无需手动指定，最终每个objectClassId会和对应的对象属性、对象名形成一行数据。

场景2：RuleManifestsProperty是结构化列（每个对象对应一个列） #

如果你的数据是普通列存储（比如列名就是1、Latitude等，每个列的结构是你说的那5个属性），用动态Unpivot实现：

DECLARE @UnpivotColumns NVARCHAR(MAX)
DECLARE @DynamicSQL NVARCHAR(MAX)

-- 动态获取需要Unpivot的列名（可根据实际规则调整匹配逻辑）
SELECT @UnpivotColumns = STRING_AGG(QUOTENAME([name]), ',')
FROM sys.columns
WHERE object_id = OBJECT_ID('YourTable')
AND [name] IN ('1', 'Latitude', 'pumpe') -- 这里可以改成更灵活的匹配，比如列名不是系统字段等

-- 生成动态Unpivot语句
SET @DynamicSQL = CONCAT(
   'SELECT objectClassId, ObjectName, propertyAlias, propertyModelDtid, propertyModelLegacy, propertyType, regexExtension
    FROM YourTable
    UNPIVOT (
        (propertyAlias, propertyModelDtid, propertyModelLegacy, propertyType, regexExtension)
        FOR ObjectName IN (', @UnpivotColumns, ')
    ) AS unpvt'
)

EXEC sp_executesql @DynamicSQL

其他数据库适配说明 #

如果用PostgreSQL，把OPENJSON换成jsonb_each；MySQL用JSON_TABLE，核心逻辑都是先动态获取对象/列名，再拼接SQL实现行转列。

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