Skip to content
Advertisement

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

An example of what this ends up looking like:

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

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:

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:

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

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

Advertisement

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:

Statement:

Result:

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement