Azure Synapse 如何交叉应用 JSON 路径 [英] Azure Synapse how to CROSS APPLY JSON PATH

问题描述

我想根据另一个表中的几个字段动态提取数据行，并在将其作为单行加入时将其汇总为 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]

<头>

Col1	Col2
1	我
2	ii
3	iii

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

<头>

A_id	B_Col1	B_Col2
1	b11	b12
1	b111	b112
2	b21	b22
2	b22	b222

查询:

      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)

<头>

Col1	Col2	B_JSON
1	我	[{A_id":1，B_Col1":b11"，B_Col2":b12"}，{A_id":1，B_Col1":b111",B_Col2":b112"}]
2	ii	[{A_id":2，B_Col1":b21"，B_Col2":b22"}，{A_id":2，B_Col1":b22",B_Col2":b222"}]
3	iii	NULL

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;

这篇关于Azure Synapse 如何交叉应用 JSON 路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！