Using BOTH scalar values and JSON objects as JSON values

Question

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

Zhorov · Accepted Answer · 2020-03-25 09:49:58Z

1

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"}]}]

edited Mar 25, 2020 at 9:49

answered Mar 25, 2020 at 6:59

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using BOTH scalar values and JSON objects as JSON values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related