json - SQL Server 2016 - FOR JSON PATH 等同于 FOR JSON AUTO

Question

使用 SQL Server 2016(在 Azure 上)我希望辅助 t2 数据作为数组，以避免主数据重复。像这样:

但我只能用 FOR JSON AUTO 来做，不能控制属性名...

我如何使用 FOR JSON PATH 或类似方法来控制属性名称？

用于测试的SQL:

create table #t1 (id bigint, name varchar(20))
create table #t2 (id bigint, idBase bigint, name varchar(20))

insert into #t1 values (1,'teste1')
insert into #t1 values (2,'teste2')
insert into #t2 values (1,1,'teste11')
insert into #t2 values (2,1,'teste21')
insert into #t2 values (3,2,'teste32')

select
    t1.id as 'base.id'
    ,t1.name as 'base.name'
    ,t2.id as 'base.secondary.id'
    ,t2.name as 'base.secondary.name'
from
    #t1 as t1
inner join 
    #t2 as t2 on t1.id = t2.idBase
for json auto

谢谢!

Answer 1

使用“for json auto”:

select  t1.id as [base.id],
        t1.name as [base.name],
        (
          select  t2.id as [base.secondary.id],
                  t2.name as [base.secondary.name]
            from  #t2 as t2 
            where t2.idBase = t1.id
            for json auto
        ) as [t2]
  from  #t1 as t1
  for json auto

结果:

[{
    "base.id": 1,
    "base.name": "teste1",
    "t2": [{
        "base.secondary.id": 1,
        "base.secondary.name": "teste11"
    },
    {
        "base.secondary.id": 2,
        "base.secondary.name": "teste21"
    }]
},
{
    "base.id": 2,
    "base.name": "teste2",
    "t2": [{
        "base.secondary.id": 3,
        "base.secondary.name": "teste32"
    }]
}]

使用“用于 json 路径”:

select  t1.id as [base.id],
        t1.name as [base.name],
        (
          select  t2.id as [id],
                  t2.name as [name]
            from  #t2 as t2 
            where t2.idBase = t1.id
            for json path
        ) as [secondary]
  from  #t1 as t1
  for json path

结果:

[{
    "base": {
        "id": 1,
        "name": "teste1"
    },
    "secondary": [{
        "id": 1,
        "name": "teste11"
    },
    {
        "id": 2,
        "name": "teste21"
    }]
},
{
    "base": {
        "id": 2,
        "name": "teste2"
    },
    "secondary": [{
        "id": 3,
        "name": "teste32"
    }]
}]