Azure Synapse 如何交叉应用 JSON 路径

2021-09-10 00:00:00 json tsql sql-server azure-synapse

我想根据另一个表中的几个字段动态提取数据行，并在将其作为单行加入时将其汇总为 JSON.

I'd like to dynamically pull rows of data based on a few fields from another table and summarize it as JSON when joining it in as a single row.

这是一个小例子来说明.

Here's a small example to illustrate.

[测试].[dbo].[tableA]

<头>
Col1Col2
1我
2ii
3iii

[测试].[dbo].[tableB]

<头>
A_idB_Col1B_Col2
1b11b12
1b111b112
2b21b22
2b22b222

查询:

      SELECT * FROM [Test].[dbo].[tableA] as A
        CROSS APPLY (
          SELECT (
              SELECT * FROM [Test].[dbo].[tableB] as B
              WHERE B.A_id = A.Col1
              FOR JSON PATH
          ) as B_JSON
      ) as CA

结果(在 SQL Server 中符合预期)

Result (as expected in SQL Server)

<头>
Col1Col2B_JSON
1我[{A_id":1，B_Col1":b11"，B_Col2":b12"}，{A_id":1，B_Col1":b111",B_Col2":b112"}]
2ii[{A_id":2，B_Col1":b21"，B_Col2":b22"}，{A_id":2，B_Col1":b22",B_Col2":b222"}]
3iiiNULL

Azure Synapse 无服务器 SQL 池中的结果:

该查询引用了分布式中不支持的对象处理方式.

The query references an object that is not supported in distributed processing mode.

问题是，它不喜欢 FOR JSON 结果周围的 SELECT，但我们需要它来分配一个列名，以便交叉应用工作.

Trouble is, it doesn't like the SELECT around the FOR JSON result, but we need that to assign a column name such that the Cross Apply works.

问题是这样的；在这种情况下实现这一目标的最佳方法是什么?

Question is thus; what is the best way to achieve this within this context?

推荐答案

我无法在您的环境中对此进行测试，因此这可能不起作用...您可以尝试以下方法之一:

I cannot test this in your environment, so this might not work... You can try one of these:

DECLARE @tblA TABLE(Col1 INT, Col2 VARCHAR(10));
INSERT INTO @tblA(Col1,Col2) VALUES
 (1,'i')
,(2,'ii')
,(3,'iii');

DECLARE @tblB TABLE(A_id INT,B_Col1 VARCHAR(10),B_Col2 VARCHAR(10));
INSERT INTO @tblB(A_id,B_Col1,B_Col2) VALUES
 (1,'b11','b12')
,(1,'b111','b112')
,(2,'b21','b22')
,(2,'b22','b222');

--在 CA 名称后面传递列名称(避免嵌套的 SELECT)

--Pass the column's name behind the CA's name (avoids the nested SELECT)

  SELECT * FROM @tblA as A
    CROSS APPLY (
          SELECT * FROM @tblB as B
          WHERE B.A_id = A.Col1
          FOR JSON PATH
      ) CA(B_JSON);

--使用标量子选择完全避免 CA

--Avoid the CA totally by using a scalar sub-select

  SELECT A.Col1
        ,A.Col2
        ,(
          SELECT * FROM @tblB as B
          WHERE B.A_id = A.Col1
          FOR JSON PATH
         ) AS B_JSON
  FROM @tblA as A;

相关文章