title: SQL to JSON - Grouping Results into JSON Array
body: <p>I am trying to come up with an SQL solution for arranging output to match an expected JSON format.   </p>
  
  <p>I have some simple SQL to highlight where the issue is coming from;</p>
  
  <pre><code>SELECT TOP 1 'Surname' AS 'name.family'
  ,'Forename, Middle Name' AS 'name.given'
  ,'Title' AS 'name.prefix'
  ,getDATE() AS 'birthdate'
  ,'F' AS 'gender'
  ,'Yes' AS 'active'
  ,'work' AS 'telecom.use'
  ,'phone' AS 'telecom.system'
  ,'12344556' AS 'telecom.value'
  FROM tblCustomer
  FOR json path
  </code></pre>
  
  <p>Which will return JSON as;</p>
  
  <pre><code>[
  {
      "name": {
          "family": "Surname",
          "given": "Forename, Middle Name",
          "prefix": "Title"
      },
      "birthdate": "2019-02-13T12:06:45.490",
      "gender": "F",
      "active": "Yes",
      "telecom": {
          "use": "work",
          "system": "phone",
          "value": "12344556"
      }
  }
  ]
  </code></pre>
  
  <p>What I need to is to add extra objects into the "telecome" array so it would appear as;</p>
  
  <pre><code>[
  {
      "name": {
          "family": "Surname",
          "given": "Forename, Middle Name",
          "prefix": "Title"
      },
      "birthdate": "2019-02-13T12:06:45.490",
      "gender": "F",
      "active": "Yes",
      "telecom": {
          "use": "work",
          "system": "phone",
          "value": "12344556"
      },
      {
          "use": "work",
          "system": "home",
          "value": "12344556"
      },
  }
  ]
  </code></pre>
  
  <p>I have incorrectly assume I could keep adding to my SQL as follows;</p>
  
  <pre><code>SELECT TOP 1 'Surname' AS 'name.family'
  ,'Forename, Middle Name' AS 'name.given'
  ,'Title' AS 'name.prefix'
  ,getDATE() AS 'birthdate'
  ,'F' AS 'gender'
  ,'Yes' AS 'active'
  ,'work' AS 'telecom.use'
  ,'phone' AS 'telecom.system'
  ,'12344556' AS 'telecom.value'
  ,'home' AS 'telecom.use'
  FROM tblCustomer
  FOR json path
  </code></pre>
  
  <p>And it would nest the items as per my naming indents however;</p>
  
  <blockquote>
    <p>Property 'telecom.use' cannot be generated in JSON output due to a
    conflict with another column name or alias. Use different names and
    aliases for each column in SELECT list.</p>
  </blockquote>
  
  <p>Is there a way to handle this nesting with SQL or will I need to create separate for JSON queries and merge them?</p>
  
  <p>Thanks</p>
  
  <blockquote>
    <p>Using @@Version Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) 
    Aug 22 2017 17:04:49   Copyright (C) 2017 Microsoft Corporation 
    Express Edition (64-bit) on Windows Server 2012 R2 Datacenter 6.3
     (Build 9600: ) (Hypervisor)</p>
  </blockquote>
  
  <p>Small edit to the question to use dynamic values rather than forced static members.  </p>
  
  <pre><code>SELECT TOP 1 'Surname' AS 'name.family'
      ,'Forename, Middle Name' AS 'name.given'
      ,'Title' AS 'name.prefix'
      ,getDATE() AS 'birthdate'
      ,'F' AS 'gender'
      ,'Yes' AS 'active'
      ,'work' AS 'telecom.use'
      ,'phone' AS 'telecom.system'
      ,customerWorkTelephone AS 'telecom.value'
      ,'home' AS 'telecom.use'
      ,'phone' AS 'telecom.system'
      ,customerHomeTelephone AS 'telecom.value'
  FROM tblCustomer
  FOR json path
  </code></pre>
  
  <p>The "value" items will be taken from columns within the tblCustomer table.  I've tried to make good on the responses below but cant get the logic quite correct in the sub query.</p>
  
  <p>Thanks again</p>
  
  <p>FURTHER EDIT </p>
  
  <p>I have some SQL that is giving me the output I expect however I am not sure its the best that it could be, is my approach less than optimal?</p>
  
  <pre><code>SELECT TOP 1 [name.family] = 'Surname'
  ,[name.given] = 'Forename, Middle Name'
  ,[name.prefix] = 'Title'
  ,[birthdate] = GETDATE()
  ,[gender] = 'F'
  ,[active] = 'Yes'
  ,[telecom] = (
      SELECT [use] = V.used
          ,[system] = 'phone'
          ,[value] = CASE V.used
              WHEN 'work'
                  THEN cu.customerWorkTelephone
              WHEN 'home'
                  THEN cu.customerHomeTelephone
              when 'mobile'
                  then cu.customerMobileTelephone
              END
      FROM (
          VALUES ('work')
              ,('home')
              ,('mobile')
          ) AS V(used)
  
      FOR json path
      )
  FROM tblCustomer cu
  FOR JSON PATH
  </code></pre>