Using BOTH scalar values and JSON objects as JSON values

I have a local table variable that I’m trying to populate with JSON key-value pairs. Sometimes the values are themselves JSON strings

DECLARE @Values TABLE
    (
        JsonKey varchar(200),
        JsonValue varchar(max)
    )

An example of what this ends up looking like:

+---------+--------------------------------------+
| JsonKey |              JsonValue               |
+---------+--------------------------------------+
| foo     | bar                                  |
| foo     | [{"label":"fooBar","Id":"fooBarId"}] |
+---------+--------------------------------------+

After populating it, I attempt to build it all up into a single JSON string, like so:

DECLARE @Json JSON = 
    (
        SELECT  V.JsonKey as 'name',
                V.JsonValue as 'value'
        FROM    @Values V
        for json path
    )

The problem with this is that it turns the JSON values into a string, rather than treating them as JSON. This results in those values not being parsed correctly.

An example of what it ends up looking like:

[
    {
        "name": "foo",
        "value": "bar"
    },
    {
        "name": "foo",
        "value": "[{"label":"fooBar","Id":"fooBarId"}]"
    }
]

I am trying to get the JSON for the second value to NOT be escaped or wrapped in double quotes. What I would like to see is this:

[
    {
        "name": "foo",
        "value": "bar"
    },
    {
        "key": "foo",
        "value": [
            {
                "label": "fooBar",
                "Id": "fooBarId"
            }
        ]
    }
]

If that value will ONLY ever be JSON, I can instead use JSON_QUERY() in the JSON build-up, like this:

DECLARE @Json JSON = 
    (
        SELECT  V.JsonKey as 'name',
                JSON_QUERY(V.JsonValue) as 'value'
        FROM    @Values V
        for json path
    )

Building it up like this gives me the result I want, but errors when the JsonValue column is not valid JSON. I attempted to put it in a case statement, to only use JSON_QUERY() when JsonValue was valid JSON, but since case statements are required to always output the same type, it turned it into a string again, and I got a repeat of the first example. I have not been able to find an elegant solution to this, and it really feels like there should be one that I’m just missing. Any help will be appreciated

Answer

One possible approach is to generate a statement with duplicate column names (JsonValue). By default FOR JSON AUTO does not include NULL values in the output, so the result is the expected JSON. Just note, that you must not use INCLUDE_NULL_VALUES in the statement or the final JSON will contain duplicate keys.

Table:

DECLARE @Values TABLE (
   JsonKey varchar(200),
   JsonValue varchar(max)
)
INSERT INTO @Values 
   (JsonKey, JsonValue)
VALUES
   ('foo', 'bar'),
   ('foo', '[{"label":"fooBar","Id":"fooBarId"}]')

Statement:

SELECT
   JsonKey AS [name],
   JSON_QUERY(CASE WHEN ISJSON(JsonValue) = 1 THEN JSON_QUERY(JsonValue) END) AS [value],
   CASE WHEN ISJSON(JsonValue) = 0 THEN JsonValue END AS [value]
FROM @Values   
FOR JSON AUTO

Result:

[{"name":"foo","value":"bar"},{"name":"foo","value":[{"label":"fooBar","Id":"fooBarId"}]}]