SQL Generate JSON string using FOR JSON PATH with 2 ROOTs

Question

I have a very simple table containing 5 columns and the table will only hold 1 record at a time. I'm to generate a JSON string from the record and send it to an endpoint.

This is how the JSON string are to be formatted. As you can see it contains 2 'roots' and this is giving me a hard time getting the correct format

{
    "fields": [
        {
            "fieldName": "Brand",
            "values": [
                "FORD"
            ]
        },
        {
            "fieldName": "Engine",
            "values": [
                "V12"
            ]
        },
        {
            "fieldName": "Location",
            "values": [
                "Monaco"
            ]
        }
    ],
    "categories": [
        {
            "fieldName": "Colour",
            "values": [
                [
                    {
                        "name": "Blue"
                    }
                ]
            ]
        },
        {
            "fieldName": "Interior",
            "values": [
                [
                    {
                        "name": "Red"
                    }
                ]
            ]
        }
    ]
}

This is my table containing the 5 columns

I have managed to create 2 separate SQL queries to get the JSON string. But I can't figure out how do it in one select.

SELECT ( 
    SELECT X.* FROM (
    SELECT CASE WHEN CarName IS NOT NULL THEN 'Brand' ELSE NULL END AS fieldName,
           CarName AS [value]
    FROM [dbo].[JSONBODY] 
    UNION
    SELECT CASE WHEN Engine IS NOT NULL THEN 'Engine' ELSE NULL END AS fieldName, 
           Engine AS [value] 
    FROM [dbo].[JSONBODY] 
    UNION
    SELECT CASE WHEN [location] IS NOT NULL THEN 'Location' ELSE NULL END AS fieldName, 
           [Location] AS [value]
    FROM [dbo].[JSONBODY] ) X
FOR JSON PATH, ROOT('fields'))
    
SELECT (
    SELECT Y.* FROM (
    SELECT CASE WHEN Colour IS NOT NULL THEN 'Colour' ELSE NULL END AS fieldName,
           JSON_QUERY('[["' + Colour + '"]]') AS 'value.name'
    FROM [dbo].[JSONBODY]  
    UNION
    SELECT CASE WHEN Interior IS NOT NULL THEN 'Interior' ELSE NULL END AS fieldName, 
           JSON_QUERY('[["' + Interior + '"]]') AS 'value.name'
    FROM [dbo].[JSONBODY]) Y 
FOR JSON PATH, ROOT('categories'))

And here are the 2 JSON strings:

{"fields":[{"fieldName":"Brand","value":"Ford"},{"fieldName":"Engine","value":"V6"},{"fieldName":"Location","value":"Boston"}]}

{"categories":[{"fieldName":"Colour","value":{"name":[["Blue"]]}},{"fieldName":"Interior","value":{"name":[["Black"]]}}]}

Question 1:
Is it possible to create the JSON string through a single SQL Select? And how can I do it?

Question 2:
If a column value is NULL it is excluded automatically from the JSON string. But I had to add the fieldName to the select and had hoped it would have exclude it from the JSON string if the corresponding field was NULL. However it creates a {}, in the JSON string. And this is not accepted when calling the endpoint. So is there another way to do it when a column value is NULL? I can of course delete it from the JSON string afterwards....

Hope the above makes sense

While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky, Dec 30 '21 at 20:36

score 1 · Answer 1 · answered Dec 30 '21 at 21:05

To do it as a single SELECT you can just UNION ALL the two results together

You can unpivot the values, then check them afterwards for nulls.

Unfortunately, SQL Server does not have JSON_AGG, so you have to bodge it with STRING_AGG and STRING_ESCAPE

SELECT
  v.fieldName,
  value = JSON_QUERY('[' + STRING_AGG('"' + STRING_ESCAPE(v.value, 'json') + '"', ',') + ']')
FROM [dbo].[JSONBODY] jb
CROSS APPLY (VALUES
    ('Brand',    jb.Brand),
    ('Engine',   jb.Engine),
    ('Location', jb.Location)
) v(fieldName, value)
GROUP BY
  v.fieldName
FOR JSON PATH, ROOT('fields');

UNION ALL

SELECT
  v.fieldName,
  [value.name] = JSON_QUERY('[[' + STRING_AGG('"' + STRING_ESCAPE(v.value, 'json') + '"', ',') + ']]')
FROM [dbo].[JSONBODY] jb
CROSS APPLY (VALUES
    ('Colour',   jb.Colour),
    ('Interior', jb.Interior)
) v(fieldName, value)
GROUP BY
  v.fieldName
FOR JSON PATH, ROOT('categories');

If you know you will only ever have one row, you can simplify it by removing the GROUP BY

SELECT (
SELECT
  v.fieldName,
  value = JSON_QUERY('["' + STRING_ESCAPE(v.value, 'json') + '"]')
FROM [dbo].[JSONBODY] jb
CROSS APPLY (VALUES
    ('Brand',    jb.Brand),
    ('Engine',   jb.Engine),
    ('Location', jb.Location)
) v(fieldName, value)
WHERE v.value IS NOT NULL
FOR JSON PATH, ROOT('fields')
)

UNION ALL

SELECT (
SELECT
  v.fieldName,
  [value.name] = JSON_QUERY('[["' + STRING_ESCAPE(v.value, 'json') + '"]]')
FROM [dbo].[JSONBODY] jb
CROSS APPLY (VALUES
    ('Colour',   jb.Colour),
    ('Interior', jb.Interior)
) v(fieldName, value)
WHERE v.value IS NOT NULL
FOR JSON PATH, ROOT('categories')
);

db<>fiddle

SQL Generate JSON string using FOR JSON PATH with 2 ROOTs

1 Answers1