How to convert Table to nested JSON in SQL Server 2019

Question

I have a working procedure that accepts a JSON string and parses it into a table as expected. What I need now is a way to reverse that; I need a procedure and/or function that can take the values inside a table and return a JSON in the same format. The structure of the table and JSON is defined, but none of the values in the table are known.

Simplified example:

DECLARE @MapTableInDB TABLE (
     Field  NVARCHAR(128)
    ,SType  NVARCHAR(128)
    ,NType  NVARCHAR(128)
    ,Source NVARCHAR(128));
DECLARE @JSON_MapFromApp NVARCHAR(MAX) = N'
{
     "FullName":           {"NType":"System.String",   "SType":"NVARCHAR(128)", "Source":"Name"}
    ,"EmployeeID":         {"NType":"System.String",   "SType":"NVARCHAR(128)", "Source":"Emplid"}
    ,"FirstName":          {"NType":"System.String",   "SType":"NVARCHAR(128)", "Source":"First Name"}
    ,"MiddleName":         {"NType":"System.String",   "SType":"NVARCHAR(128)", "Source":"Middle Name"}
    ,"LastName":           {"NType":"System.String",   "SType":"NVARCHAR(128)", "Source":"Last Name"}
}
';
INSERT INTO @MapTableInDB (Field,SType,NType,Source)
SELECT   j.[key]     AS Field
        ,m.SType     AS SType
        ,m.NType     AS NType
        ,m.Source    AS Source
FROM OPENJSON(@JSON_MapFromApp, '$') j
CROSS APPLY (SELECT * FROM OPENJSON(j.[value], '$') WITH (
     Source NVARCHAR(128) '$.Source'
    ,SType  NVARCHAR(128) '$.SType'
    ,NType  NVARCHAR(128) '$.NType'
)) AS m;

This works as expected and creates a table like:

Field           SType           NType           Source
--------------- --------------- --------------- ---------------
FullName        NVARCHAR(128)   System.String   Name
EmployeeID      NVARCHAR(128)   System.String   Emplid
FirstName       NVARCHAR(128)   System.String   First Name
MiddleName      NVARCHAR(128)   System.String   Middle Name
LastName        NVARCHAR(128)   System.String   Last Name

So far, my attempts to recreate the original JSON string are close, but need assistance:

SELECT   m.field
        ,(  SELECT m2.SType, m2.NType, m2.Source
            FROM @MapTableInDB m2
            WHERE (m2.Field = m.Field)
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS '$'
FROM @MapTableInDB m
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

Result:

/*
{"field":"FullName","$":"{\"SType\":\"NVARCHAR(128)\",\"NType\":\"System.String\",\"Source\":\"Name\"}"}
,{"field":"EmployeeID","$":"{\"SType\":\"NVARCHAR(128)\",\"NType\":\"System.String\",\"Source\":\"Emplid\"}"}
,{"field":"FirstName","$":"{\"SType\":\"NVARCHAR(128)\",\"NType\":\"System.String\",\"Source\":\"First Name\"}"}
,{"field":"MiddleName","$":"{\"SType\":\"NVARCHAR(128)\",\"NType\":\"System.String\",\"Source\":\"Middle Name\"}"}
,{"field":"LastName","$":"{\"SType\":\"NVARCHAR(128)\",\"NType\":\"System.String\",\"Source\":\"Last Name\"}"}
*/

My use case is a client app1 uploads the JSON formatted table structure via procedure1, then executes procedure2 to [re]build the table (working). Then client app2 fetches the JSON formatted table mapping (above issue) and then uploads content to the table created by procedure2.
The issue I need assistance with is making the return JSON be identical (semantically) to how it was submitted.

For context: I use it in client app2 as a definition for a nested hash table to transform a CSV into a datatable (works fine as long as it is formatted exactly the same as the submission). I do not want to store the raw JSON because I have other processes that add and remove columns outside the original app1 submission.

Answer 1

Unfortunately, SQL Server 2019 doesn't have the built in JSON tools to achieve this as you want. As such you'll actually need to use string aggregation to make your JSON instead. You can, however, still make the JSON object using FOR JSON PATH:

WITH CTE AS (
    SELECT MT.Field,
           (SELECT MT.SType, MT.NType, MT.Source FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS FieldDetails
    FROM @MapTableInDB MT)
SELECT '{' + 
       STRING_AGG(CONCAT('"' + STRING_ESCAPE(C.Field,'JSON'),'":',C.FieldDetails),',')
       + '}'
FROM CTE C

This gives the following value:

{"FullName":{"SType":"NVARCHAR(128)","NType":"System.String","Source":"Name"},"EmployeeID":{"SType":"NVARCHAR(128)","NType":"System.String","Source":"Emplid"},"FirstName":{"SType":"NVARCHAR(128)","NType":"System.String","Source":"First Name"},"MiddleName":{"SType":"NVARCHAR(128)","NType":"System.String","Source":"Middle Name"},"LastName":{"SType":"NVARCHAR(128)","NType":"System.String","Source":"Last Name"}}

Which, when "prettified" as JSON, gives something like:

{
    "FullName": {
        "SType": "NVARCHAR(128)",
        "NType": "System.String",
        "Source": "Name"
    },
    "EmployeeID": {
        "SType": "NVARCHAR(128)",
        "NType": "System.String",
        "Source": "Emplid"
    },
    "FirstName": {
        "SType": "NVARCHAR(128)",
        "NType": "System.String",
        "Source": "First Name"
    },
    "MiddleName": {
        "SType": "NVARCHAR(128)",
        "NType": "System.String",
        "Source": "Middle Name"
    },
    "LastName": {
        "SType": "NVARCHAR(128)",
        "NType": "System.String",
        "Source": "Last Name"
    }
}