Create nested JSON arrays using FOR JSON PATH

Question

I need to create a JSON output from a query that uses inner join between two tables with a one to many relationship.
I would like the values of the secondary table to be nested as array properties of the primary table.

Consider the following example:

DECLARE @Persons AS TABLE (     person_id int primary key,     person_name varchar(20) )  DECLARE @Pets AS TABLE (     pet_owner int, -- in real tables, this would be a foreign key     pet_id int  primary key,     pet_name varchar(10) )  INSERT INTO @Persons (person_id, person_name) VALUES (2, 'Jack'), (3, 'Jill')  INSERT INTO @Pets (pet_owner, pet_id, pet_name) VALUES (2, 4, 'Bug'), (2, 5, 'Feature'), (3, 6, 'Fiend') 

And query:

DECLARE @Result as varchar(max) SET @Result = ( SELECT  person_id as [person.id],         person_name as [person.name],         pet_id as [person.pet.id],         pet_name as [person.pet.name] FROM @Persons  JOIN @Pets ON person_id = pet_owner FOR JSON PATH, ROOT('pet owners') )  PRINT @Result 

This will print the following JSON:

{     "pet owners":     [     {"person":{"id":2,"name":"Jack","pet":{"id":4,"name":"Bug"}}},     {"person":{"id":2,"name":"Jack","pet":{"id":5,"name":"Feature"}}},     {"person":{"id":3,"name":"Jill","pet":{"id":6,"name":"Fiend"}}}     ] } 

However, I would like to have the pets data as arrays inside the owners data:

{     "pet owners":     [         {             "person":             {                 "id":2,"name":"Jack","pet":                 [                     {"id":4,"name":"Bug"},                     {"id":5,"name":"Feature"}                 ]             }         },         {             "person":             {                 "id":3,"name":"Jill","pet":                 {"id":6,"name":"Fiend"}             }         }     ] } 

How can I do this?

Answer 1

You can use the following query:

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') 

For more information, see https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/10/09/returning-child-rows-formatted-as-json-in-sql-server-queries/

Answer 2

With deeply nested arrays the subqueries get unmanageable quickly:

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

I create a relational (non-json) view that joins all my tables and has the json structure embedded in the column aliases, just like for json path does. But I also have [] to indicate that the json node is an array. Like this:

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 ..... 

I wrote a stored procedure that creates a json view into the non-json view that parses the column names of the relational view and makes the json pretty. See below. Call it with the name of your relational view and it creates a view. It's not thoroughly tested but it works for me. Only caveat is that tables need to have id columns called id. It uses string_agg() and json_array() to the version of sql needs to be pretty new. It's also set up to return an array in the root. It will need tweaking to return an object.

 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