title: FOR JSON PATH vs FOR JSON AUTO SQL Server
body: <p>I'm having an issue creating nested JSON in SQL Server.  I'm trying to create an output that looks like this:</p>
  
  <pre><code>[
    {
      "websiteURL": "www.test.edu",
      "email": "hello@test.edu",
      "phone": 123456798,
      "address": {
          "address1": "1 Oak Grove",
          "address2": "London",
          "address3": "UK"
      },
      "accreditations": [
        {
          "name": "Indicator1",
          "value": "True"
        },
        {
          "name": "Indicator2",
          "value": "False"
        },
        {
          "name": "Indicator3",
          "value": "False"
        }
      ]
    }
  ]
  </code></pre>
  
  <p>I've tried both FOR JSON AUTO and FOR JSON PATH:</p>
  
  <pre><code>SELECT
    d.SCHOOL_WEBSITE AS websiteURL
    ,d.SCHOOL_EMAIL AS email 
   ,d.SCHOOL_TELEPHONE AS phone
   ,d.[Address 1] AS 'address.address1'
   ,d.[Address 2] AS 'address.address2'
   ,d.[Address 3] AS 'address.address3'
   ,accreditations.[IndiUID] as name   
   ,accreditations.Value as value 
   FROM [TESTDB].[dbo].[DataValues] as d,[TESTDB].[dbo].[accreditations] as accreditations
   WHERE d.Code = accreditations.SchoolCode
   FOR JSON AUTO --PATH
  </code></pre>
  
  <p>FOR JSON AUTO creates this (address section is not nested (but accredidation is):</p>
  
  <pre><code>[
    {
      "websiteURL": "www.test.edu",
      "email": "hello@test.edu",
      "phone": 123456798,
      "address.address1": "1 Oak Grove",
      "address.address2": "London",
      "address.address3": "UK",
      "accreditations": [
        {
          "name": "Indicator1",
          "value": "True"
        },
        {
          "name": "Indicator2",
          "value": "False"
        },
        {
          "name": "Indicator3",
          "value": "False"
        }
      ]
    }
  ]
  </code></pre>
  
  <p>FOR JSON PATH creates this (address section is nested, but accreditation is not - the whole block repeats):</p>
  
  <pre><code>[
    {
      "websiteURL": "www.test.edu",
      "email": "hello@test.edu",
      "phone": 123456798,
      "address": {
        "address1": "1 Oak Grove",
        "address2": "London",
        "address3": "UK"
      },
      "name": "Indicator1",
      "value": "True"
    },
    {
      "websiteURL": "www.test.edu",
      "email": "hello@test.edu",
      "phone": 123456798,
      "address": {
        "address1": "1 Oak Grove",
        "address2": "London",
        "address3": "UK"
      },
      "name": "Indicator2",
      "value": "False"
    },
    {
      "websiteURL": "www.test.edu",
      "email": "hello@test.edu",
      "phone": 123456798,
      "address": {
        "address1": "1 Oak Grove",
        "address2": "London",
        "address3": "UK"
      },
      "name": "Indicator3",
      "value": "False"
      }
  ]
  </code></pre>
  
  <p>I think the key to it is some sort of FOR JSON sub query around the accreditations but I haven't had any success with this.</p>
  
  <p>Create sample data with the following:</p>
  
  <pre><code>    /****** Object:  Table [dbo].[accreditations]    Script Date: 11/09/2018 22:29:43 ******/
  CREATE TABLE [dbo].[accreditations](
      [SchoolCode] [nvarchar](255) NULL,
      [IndiUID] [nvarchar](255) NULL,
      [Value] [nvarchar](255) NULL
  ) ON [PRIMARY]
  GO
  /****** Object:  Table [dbo].[DataValues]    Script Date: 11/09/2018 22:29:44 ******/
  CREATE TABLE [dbo].[DataValues](
      [Code] [nvarchar](255) NULL,
      [SCHOOL_NAME_FORMAL] [nvarchar](255) NULL,
      [SCHOOL_WEBSITE] [nvarchar](255) NULL,
      [SCHOOL_EMAIL] [nvarchar](255) NULL,
      [SCHOOL_TELEPHONE] [float] NULL,
      [Address 1] [nvarchar](255) NULL,
      [Address 2] [nvarchar](255) NULL,
      [Address 3] [nvarchar](255) NULL
  ) ON [PRIMARY]
  GO
  INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'ABC', N'Indicator1', N'True')
  GO
  INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'ABC', N'Indicator2', N'False')
  GO
  INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'ABC', N'Indicator3', N'False')
  GO
  INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'DEF', N'Indicator1', N'True')
  GO
  INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'DEF', N'Indicator2', N'False')
  GO
  INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'DEF', N'Indicator3', N'False')
  GO
  INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'GHI', N'Indicator1', N'True')
  GO
  INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'GHI', N'Indicator2', N'True')
  GO
  INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'GHI', N'Indicator3', N'True')
  GO
  INSERT [dbo].[DataValues] ([Code], [SCHOOL_NAME_FORMAL], [SCHOOL_WEBSITE], [SCHOOL_EMAIL], [SCHOOL_TELEPHONE], [Address 1], [Address 2], [Address 3]) VALUES (N'ABC', N'test', N'www.test.edu', N'hello@test.edu', 123456798, N'1 Oak Grove', N'London', N'UK')
  GO
  INSERT [dbo].[DataValues] ([Code], [SCHOOL_NAME_FORMAL], [SCHOOL_WEBSITE], [SCHOOL_EMAIL], [SCHOOL_TELEPHONE], [Address 1], [Address 2], [Address 3]) VALUES (N'DEF', N'something', N'https://something.edu/fulltime', N'hello@something.edu', 987654321, N'23 Tree Road', N'Paris', N'France')
  GO
  INSERT [dbo].[DataValues] ([Code], [SCHOOL_NAME_FORMAL], [SCHOOL_WEBSITE], [SCHOOL_EMAIL], [SCHOOL_TELEPHONE], [Address 1], [Address 2], [Address 3]) VALUES (N'GHI', N'university', N'http://www.university.ac.uk/', N'hello@university.ac.uk/', 123123123, N'57 Bonsai Lane', N'London', N'UK')
  GO
  </code></pre>