SQL to generate JSON with same column names and different aliases-CodePudding

I am trying to generate JSON from my SQL data in SSMS in this format :

{
  "id": "1234",
  "first_name": "Hasan",  
  "last_name": "Mahmud",  
  "custom_fields": [
    {
      "custom_field_name": "CPRCertified",
      "custom_field_value": "Y"
    },
    {
      "custom_field_name": "IsAttorney",
      "custom_field_value": "N"
    }
  ]
}

I am trying this:

SELECT e.Empl_ID AS id,
       e.FirstName AS first_name,
       e.LastName AS last_name,
       'CPRCertified' AS [custom_fields.custom_field_name],
       e.CPRCertified AS [custom_fields.custom_field_value],
       'IsAttorney' AS [custom_fields.custom_field_name],
       e.IsAttorney AS [custom_fields.custom_field_value]
FROM #e e
WHERE e.Empl_ID = '1234'
FOR JSON PATH;

But I am getting this error:

Property 'custom_fields.custom_field_name' 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.

I have tried this topics SQL to JSON - Grouping Results into JSON Array but does not work as I have same "custom_field_name" multiple times.

CodePudding user response：

One method would be to UNPIVOT your data (using a VALUES table construct) and then switch to JSON AUTO:

SELECT e.Empl_ID AS id,
       e.FirstName AS first_name,
       e.LastName AS last_name,
       custom_fields.custom_field_name,
       custom_fields.custom_field_value
FROM #e e
     CROSS APPLY (VALUES(N'CPRCertified',e.CPRCertified),
                        (N'IsAttorney',e.IsAttorney))custom_fields(custom_field_name,custom_field_value)
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER;

CodePudding user response：

Another possible approach is to generate the nested JSON object ('$."custom_fields"') for each row and then build the final JSON:

SELECT 
   e.Empl_ID AS id,
   e.FirstName AS first_name,
   e.LastName AS last_name,
   JSON_QUERY(
      (
      SELECT * 
      FROM (VALUES
         ('CPRCertified', e.CPRCertified),
         ('IsAttorney', e.IsAttorney)
      ) v (custom_field_name, custom_field_value)
      FOR JSON PATH
      )
   ) AS custom_fields
FROM (VALUES
   ('1234', 'Hasan', 'Mahmud', 'Y', 'N')
) e (Empl_ID, FirstName, LastName, CPRCertified, IsAttorney)
WHERE e.Empl_ID = '1234'
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER