title: Create nested JSON arrays using FOR JSON PATH
body: <p>I need to create a JSON output from a query that uses inner join between two tables with a one to many relationship.<br>
  I would like the values of the secondary table to be nested as array properties of the primary table.</p>
  
  <p>Consider the following example:</p>
  
  <pre><code>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')
  </code></pre>
  
  <p>And query:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>This will print the following JSON:</p>
  
  <pre><code>{
      "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"}}}
      ]
  }
  </code></pre>
  
  <p>However, I would like to have the pets data as arrays inside the owners data:</p>
  
  <pre><code>{
      "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"}
              }
          }
      ]
  }
  </code></pre>
  
  <p>How can I do this?  </p>