SQL导入脚本STRING_AGG格式调整问题求助

我正在开发一个SQL导入脚本，其余功能均正常，仅STRING_AGG部分的输出格式不符合需求。当前输出为：

00000000-0000-0000-0000-000000000000, 00000000-0000-0000-0000-000000000000

需要调整为JSON数组格式：

["00000000-0000-0000-0000-000000000000","00000000-0000-0000-0000-000000000000"]

尝试过使用CONCAT函数但未得到任何输出，求实现方法。相关SQL代码如下：

WITH CTE_EnrichmentProperty (EntityId, EnrichmentIndex, IbProductKey) AS (
 SELECT DISTINCT EntityId
                , EnrichmentIndex
                , IbProductKey
         FROM dbo.ArticleEnrichmentImportItem
         WHERE ArticleEnrichmentImportItem.ArticleEnrichmentImportId = @ImportId
           AND ArticleEnrichmentImportItem.EnrichmentType = 'EnrichmentPropertySelection'
           AND ArticleEnrichmentImportItem.State IN (1, 2, 3)
)
    INSERT INTO Attribute (Id, Type, EnrichmentProperty_EnrichmentPropertyId, EnrichmentProperty_Value)
SELECT ArticleEnrichmentImportItem.Id,
       'enrichment',
       CTE_EnrichmentProperty.EntityId,
       (SELECT STRING_AGG(CAST(EntityId AS varchar(38)), ',') FROM dbo.ArticleEnrichmentImportItem A 
            WHERE A.ArticleEnrichmentImportId = @ImportId
            AND A.EnrichmentType = 'EnrichmentPropertySelectionValue'
            AND A.EnrichmentIndex = ArticleEnrichmentImportItem.EnrichmentIndex
            And A.IbProductKey = ArticleEnrichmentImportItem.IbProductKey
        )
FROM dbo.ArticleEnrichmentImportItem
      JOIN CTE_EnrichmentProperty ON CTE_EnrichmentProperty.EnrichmentIndex = ArticleEnrichmentImportItem.EnrichmentIndex 
    WHERE ArticleEnrichmentImportItem.ArticleEnrichmentImportId = @ImportId
      AND ArticleEnrichmentImportItem.EnrichmentType = 'EnrichmentPropertySelectionValue'
              AND ArticleEnrichmentImportItem.IbProductKey = CTE_EnrichmentProperty.IbProductKey;

核心是修改STRING_AGG的输出逻辑，给每个UUID添加双引号，再将整体包裹为JSON数组格式，同时处理无匹配数据时的空值情况：

将原子查询中的STRING_AGG部分替换为以下代码：

(SELECT COALESCE(CONCAT('[', STRING_AGG('"' + CAST(EntityId AS varchar(38)) + '"', ','), ']'), '[]') 
 FROM dbo.ArticleEnrichmentImportItem A 
 WHERE A.ArticleEnrichmentImportId = @ImportId
   AND A.EnrichmentType = 'EnrichmentPropertySelectionValue'
   AND A.EnrichmentIndex = ArticleEnrichmentImportItem.EnrichmentIndex
   And A.IbProductKey = ArticleEnrichmentImportItem.IbProductKey
)

逻辑说明： #

1. 给每个UUID加双引号：通过'"' + CAST(EntityId AS varchar(38)) + '"'将每个UUID转换为带双引号的字符串，例如"00000000-0000-0000-0000-000000000000"。
2. 拼接为逗号分隔的字符串：用STRING_AGG(..., ',')将带引号的UUID拼接成"uuid1","uuid2"格式。
3. 包裹为JSON数组：通过CONCAT('[', ..., ']')将上述字符串包裹成["uuid1","uuid2"]的数组格式。
4. 处理空值：用COALESCE(..., '[]')确保当没有匹配数据时，返回空数组[]而非NULL。

修改后的完整SQL代码：

WITH CTE_EnrichmentProperty (EntityId, EnrichmentIndex, IbProductKey) AS (
 SELECT DISTINCT EntityId
                , EnrichmentIndex
                , IbProductKey
         FROM dbo.ArticleEnrichmentImportItem
         WHERE ArticleEnrichmentImportItem.ArticleEnrichmentImportId = @ImportId
           AND ArticleEnrichmentImportItem.EnrichmentType = 'EnrichmentPropertySelection'
           AND ArticleEnrichmentImportItem.State IN (1, 2, 3)
)
    INSERT INTO Attribute (Id, Type, EnrichmentProperty_EnrichmentPropertyId, EnrichmentProperty_Value)
SELECT ArticleEnrichmentImportItem.Id,
       'enrichment',
       CTE_EnrichmentProperty.EntityId,
       (SELECT COALESCE(CONCAT('[', STRING_AGG('"' + CAST(EntityId AS varchar(38)) + '"', ','), ']'), '[]') 
        FROM dbo.ArticleEnrichmentImportItem A 
        WHERE A.ArticleEnrichmentImportId = @ImportId
          AND A.EnrichmentType = 'EnrichmentPropertySelectionValue'
          AND A.EnrichmentIndex = ArticleEnrichmentImportItem.EnrichmentIndex
          And A.IbProductKey = ArticleEnrichmentImportItem.IbProductKey
       )
FROM dbo.ArticleEnrichmentImportItem
      JOIN CTE_EnrichmentProperty ON CTE_EnrichmentProperty.EnrichmentIndex = ArticleEnrichmentImportItem.EnrichmentIndex 
    WHERE ArticleEnrichmentImportItem.ArticleEnrichmentImportId = @ImportId
      AND ArticleEnrichmentImportItem.EnrichmentType = 'EnrichmentPropertySelectionValue'
              AND ArticleEnrichmentImportItem.IbProductKey = CTE_EnrichmentProperty.IbProductKey;

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