Azure数据工厂-嵌套JSON的SQL [英] Azure Data Factory - SQL to nested JSON

问题描述

我有一个SQL数据库，其中包含用于职员和任命的表(1名职员:许多约会).我想使用Azure数据工厂以类似于以下格式将其输出到Blob存储区中的嵌套JSON:

  [{"staffid":"101"，"firstname":"Donald"，"lastname":"Duck"，任命":[{"appointmentid":"201"，"startdate":"2020-02-01T00:00:00"，结束日期":"2020-04-29T23:00:00"}，{"appointmentid":"202"，"startdate":"2020-01-01T00:00:00"，结束日期":"2020-01-31T00:00:00"}]}，{"staffid":"102"，"firstname":"Mickey"，"lastname":"Mouse"，任命":[{"appointmentid":"203"，"startdate":"2020-02-01T00:00:00"，结束日期":"2020-04-29T23:00:00"}，{"appointmentid":"204"，"startdate":"2020-01-01T00:00:00"，结束日期":"2020-01-31T00:00:00"}]}] 

我尝试使用Copy活动，但这会生成平面JSON结构，而不是上述的嵌套结构.有没有人有办法做到这一点?

解决方案

ADF中JSON数据的更多方案正在趋于平缓.但是，根据您的描述，您需要生成的JSON包含按某些列排列的Json数组组.诸如通过相同的职员合并约会对象之类的事情.

如果我的理解正确，那么您可以从以前的案例中获得一些线索:

在sql db源数据集中使用sql:

 选择app.staffid，app.firstname，app.lastname，'约会'=(选择约会ID AS为约会ID"，开始日期为开始日期"，结束日期为结束日期"从dbo.appoint作为app2其中app2.staffid = app.staffid和app2.firstname = app.firstname和app2.lastname = app.lastnameJSON路径)从dbo.appoint作为应用程序按app.staffid，app.firstname，app.lastname分组FOR JSON路径； 

blob存储中的输出:

我尝试验证json格式，并且正确无误.

I have a SQL database with tables for Staff and Appointments (1 staff : many appointments). I'd like to use Azure Data Factory to output this to nested JSON in a Blob store in a format similar to the below:

[
   {
      "staffid":"101",
      "firstname":"Donald",
      "lastname":"Duck",
      "appointments":[
         {
            "appointmentid":"201",
            "startdate":"2020-02-01T00:00:00",
            "enddate":"2020-04-29T23:00:00"
         },
         {
            "appointmentid":"202",
            "startdate":"2020-01-01T00:00:00",
            "enddate":"2020-01-31T00:00:00"
         }
      ]
   },
   {
      "staffid":"102",
      "firstname":"Mickey",
      "lastname":"Mouse",
      "appointments":[
         {
            "appointmentid":"203",
            "startdate":"2020-02-01T00:00:00",
            "enddate":"2020-04-29T23:00:00"
         },
         {
            "appointmentid":"204",
            "startdate":"2020-01-01T00:00:00",
            "enddate":"2020-01-31T00:00:00"
         }
      ]
   }
]

I've tried using the Copy activity but this produces flat JSON structures rather than the nested structure described above. Has anyone got a way to do this please?

解决方案

More scenarios for JSON data in ADF is flattening. However,according to your description,your need producing JSON contains Json array group by some columns.Something like merge appointment things by same staff.

If my understanding is right,then you could get some clues from my previous case:How to split into Sub Documents with Nesting separator?. Please refer to my test:

Simulate your sample data:

Use sql in sql db source dataset:

select app.staffid,app.firstname,app.lastname,
'appointments' = (
            SELECT
                appointmentid AS 'appointmentid',startdate as 'startdate',enddate as 'enddate'
            FROM
                dbo.appoint as app2
            where app2.staffid = app.staffid and
            app2.firstname = app.firstname and
            app2.lastname = app.lastname
            FOR JSON PATH)
from dbo.appoint as app
group by app.staffid,app.firstname,app.lastname
FOR JSON Path;

Output in blob storage:

I try to verify the json format and it is correct.

这篇关于Azure数据工厂-嵌套JSON的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！