可以使用以下查询：

SELECT pr.person_id AS [person.id], pr.person_name AS [person.name],
    (
        SELECT pt.pet_id AS id, pt.pet_name AS name 
        FROM @Pets pt WHERE pt.pet_owner=pr.person_id 
        FOR JSON PATH
    ) AS [person.pet]
FROM @Persons pr 
FOR JSON PATH, ROOT('pet owners')

有关详细信息，请参见https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/10/09/returning-child-rows-formatted-as-json-in-sql-server-queries/

对于深度嵌套的数组，子查询很快就变得难以管理：

select id,foo, (select id, bar, (select ... for json path) things, 
(select...) more_things) yet_more, select(...) blarg

我创建了一个连接所有表的关系（非json）视图，并在列别名中嵌入了json结构，就像表示json路径一样，但我还使用[]表示json节点是一个数组，如下所示：

select p.id [id], p.foo [foo], c.name [children[].name], c.id [children[].id],
gp.name [grandparent.name], gc.name [children[].grandchildren[].name]
from parent p
join children c on c.parent_id = p.id .....

我编写了一个存储过程，它在非json视图中创建一个json视图，解析关系视图的列名，使json更美观。见下文。用关系视图的名称调用它，它会创建一个视图。它没有经过彻底测试，但对我来说是有效的。唯一需要注意的是，表需要有名为id的列。它使用string_agg（）和json_array（）**到sql版本需要是相当新的。它也被设置为在根中返回一个数组。它需要调整以返回一个对象。

create procedure create_json_from_view
@view_name varchar(max)
as

create table #doc_schema (
    node_level int,             -- nesting level starting with 0
    node_name varchar(max),     -- alias used for this nodes query
    node_path varchar(max),     -- full path to this node
    parent_path varchar(max),   -- full path to it's parents 
    is_array bit,               -- is node marked as array by ending with []
    select_columns varchar(max),-- comma separated path/alias pairs for selected columns on node
    group_by_columns varchar(max), -- comma separated paths for selected columns on node. group by is necessary to prevent duplicates
    node_parent_id varchar(max),   -- the id column path to join subquery to parent. NOTE: ID COLUMN MUST BE CALLED ID
    from_clause varchar(max),   -- from clause built from above fields
    node_query varchar(max)     -- complete query built from above fields
)

/* get each node path from view schema
*/
INSERT INTO #doc_schema (node_path)
select distinct LEFT(COLUMN_NAME,CHARINDEX('.'+ VALUE + '.',COLUMN_NAME) + LEN(VALUE)) node_path 
FROM INFORMATION_SCHEMA.COLUMNS 
CROSS APPLY STRING_SPLIT(COLUMN_NAME, '.') 
WHERE CHARINDEX('.',COLUMN_NAME) > 0
AND RIGHT(COLUMN_NAME,LEN(VALUE)) <> VALUE
and table_name = @view_name

/* node_name past rightmost period or the same as node_path if there is no period
also remove [] from arrays
*/
update #doc_schema set node_name = 
case when charindex('.',node_path) = 0 then replace(node_path,'[]','')
else REPLACE(right(node_path,charindex('.',reverse(node_path)) - 1),'[]','') end

/* if path ends with [] node is array
    escapes are necessary because [] have meaning for like
*/
update #doc_schema set is_array =
case when node_path like '%\[\]' escape '\' then 1 else 0 end --\

/* parent path is everything before last . in node path
    except when the parent is the root, in which case parent is empty string
*/
update #doc_schema set parent_path = 
case when charindex('.',node_path) = 0 then ''
else left(node_path,len(node_path) - charindex('.',reverse(node_path))) end

/* level is how many . in path. an ugly way to count.
*/
update #doc_schema set node_level = len(node_path) - len(replace(node_path,'.','')) + 1

/* set up root node
*/
insert into #doc_schema (node_path,node_name,parent_path,node_level,is_array)
select '','',null,0,1

/* I'm sorry this is so ugly. I just gave up on explaining
    all paths need to be wrapped in [] and internal ] need to be escaped as ]]
*/
update #doc_schema set select_columns = sub2.select_columns, group_by_columns = sub2.group_by_columns
from (
    select node_path,string_agg(column_path + ' ' + column_name,',') select_columns,
    string_agg(column_path,',') group_by_columns
    from (
        select ds.node_path,'['+replace(c.COLUMN_NAME,']',']]')+']' column_path,replace(c.column_name,ds.node_path + '.','') column_name
        from INFORMATION_SCHEMA.COLUMNS c
        join #doc_schema ds
        on (charindex(ds.node_path + '.', c.COLUMN_NAME) = 1
        and charindex('.',replace(c.COLUMN_NAME,ds.node_path + '.','')) = 0)
        or (ds.node_level = 0 and charindex('.',c.COLUMN_NAME) =  0)
        where table_name = @view_name
    ) sub
    group by node_path
) sub2
where #doc_schema.node_path = sub2.node_path

/* id paths for joining subqueries to parents
    Again, the need to be wrapped in [] and and internal ] need to be escaped as ]]
*/
update #doc_schema set node_parent_id  = 
    case when parent_path = '' then '[id]' 
    else '[' + replace(parent_path,']',']]')+'.id]'
    end

/* table aliases for joining subqueries to parents need to be unique
    just use L0 L1 etc based on nesting level
*/
update #doc_schema set from_clause =
    case when node_level = 0 then ' from ' + @view_name + ' L'+cast(node_level as varchar(4)) + ' '
    else ' from ' + @view_name + ' L'+cast(node_level as varchar(4))+' where L'+cast(node_level - 1 as varchar(4))+'.'+ node_parent_id + 
        '  = L'+cast(node_level as varchar(4))+'.'+ node_parent_id 
    end

/* Assemble node query from all parts
    ###subqueries### is a place to put subqueries for node
*/
update #doc_schema set  node_query = 
        ' (select ' + select_columns + ', ###subqueries###' + from_clause 
        + ' group by '+ group_by_columns
        +' for json path) '

/* json path will treat all objects as arrays so select first explicitly
    to prevent [] in json
*/  
update #doc_schema set  node_query =    
    case when is_array = 0
    then '(select JSON_query(' + node_query + ',''$[0]'')) ' + node_name
    else node_query +  + node_name end

/* starting with highest nesting level substitute child subqueries ino
    subquery hold in their parents
*/
declare @counter int = (select max(node_level) from #doc_schema)

while(@counter >= 0)
begin
    update #doc_schema set node_query = replace(node_query,'###subqueries###', subs.subqueries)
    from
    (select parent_path, string_agg(node_query,',') subqueries, node_level from #doc_schema
    group by parent_path, node_level ) subs
    where subs.node_level = @counter and 
    #doc_schema.node_path = subs.parent_path

    set @counter -= 1
end

/* objects and arrays with no subobjects or subarrays still have subquery holder so remove them
*/
update #doc_schema set node_query = replace(node_query,', ###subqueries###', '') where node_level = 0

declare @query nvarchar(max) = (select node_query from #doc_schema where node_level = 0)

/* add wrapper to query to specify column nave otherwise create view will fail
*/
set @query = 
    case when OBJECT_ID(@view_name + '_JSON', 'V') is NULL then 'create' else 'alter' end +
    ' view ' + @view_name + '_json as select' + @query + ' json'

exec sp_executesql @query

我已经按照@Razvan Socol制作了下面的json格式。

• • JSON格式**

[
      "domain_nm": "transactions",
      "tables": [
        {
          "tableName": "transactions_details",
          cols: [
            {
              "col_nm": "audit_transactions_details_guid",
              "col_data_typ": "string"
            }
          ]
        }
      ]
    ]

• • SQL语言**

select outer1.DOMAIN_NM as domain_nm,
    (select inner2.TBL_NM as tableName,
            (select inner1.COL_NM as col_nm, inner1.COL_DATA_TYP as col_data_typ
            from ONBD_MTDT.CDM_TBL inner1
            where inner1.TBL_NM=inner2.TBL_NM
            FOR JSON PATH ) as cols
    from ONBD_MTDT.CDM_TBL inner2 
    where inner2.DOMAIN_NM=outer1.DOMAIN_NM
    group by inner2.DOMAIN_NM,inner2.TBL_NM
    FOR JSON PATH ) as tables
from ONBD_MTDT.CDM_TBL outer1 
group by outer1.DOMAIN_NM
FOR JSON PATH

可以这样实现

select   OwnerFirstName,  OwnerMiddleName , OwnerLastName, OwnerNumber,     
    
    ContactOwnerMailAddressUnit 'MailingAddress.UnitNumber',
    ContactOwnerMailAddressUnitPrefix 'MailingAddress.UnitType',
    case when ContactOwnerMailAddressHouseNumber='' then '' else ContactOwnerMailAddressHouseNumber + ' ' end+
    ContactOwnerMailAddressStreetName + 
    case when ContactOwnerMailAddressStreetSuffix='' then '' else ' ' + ContactOwnerMailAddressStreetSuffix end 'MailingAddress.StreetAddress',
    ContactOwnerMailAddressCity 'MailingAddress.City',
    ContactOwnerMailAddressState 'MailingAddress.State',
    ContactOwnerMailAddressZIP 'MailingAddress.ZipCode'

    from T_Owners       
    join T_OwnersPropertiesMapping
    on T_OwnersPropertiesMapping.OwnerID = T_Owners.OwnerID     
    where T_OwnersPropertiesMapping.PropertyID=@PropertyID 
    for json path

这是结果

[
  {
    "OwnerFirstName": "Bon 7360318",
    "OwnerMiddleName": "Mr",
    "OwnerLastName": "Jovi",
    "OwnerNumber": 3,
    "MailingAddress": {
      "UnitNumber": "",
      "UnitType": "",
      "StreetAddress": "PO BOX 1736",
      "City": "BOULDER CREEK",
      "State": "CA",
      "ZipCode": "95006"
    }
  },
  {
    "OwnerFirstName": "Bon 6717425",
    "OwnerMiddleName": "Mr",
    "OwnerLastName": "Jovi",
    "OwnerNumber": 1,
    "MailingAddress": {
      "UnitNumber": "",
      "UnitType": "",
      "StreetAddress": "PO BOX 1736",
      "City": "BOULDER CREEK",
      "State": "CA",
      "ZipCode": "95006"
    }
  }
]

现在，你完全是在盲目地飞行，如果设计API的人是理智的，那么假设它将返回某种用户对象的数组可能是安全的--但是，仅仅通过查看这个端点是无法得出每个用户对象实际携带的数据的。