SQL Server 2016 - FOR JSON PATH equivalent to FOR JSON AUTO

Question

Using SQL Server 2016 (on Azure) I want the secondary t2 data as an array, to avoid the primary data repetition. Something like this:

But I can only do it with FOR JSON AUTO and cannot control the property names...

How can I do this using FOR JSON PATH or the like so I can have control over property names?

SQL for testing:

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

Thanks!

Answer 1

Using "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

Result:

[{
    "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"
    }]
}]

Using "for json path":

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

Result:

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