CODEWITHSUNDEEP
jsonsql-serverjson-query
Eric
Eric

Reputation: 131

Can you mix string and object outputs in SQL JSON?

I'm trying to construct a JSON-serialized list of key/value pair items from my SQL database (compat level 140). The trick is that the values can be anything: numbers, strings, null, or other JSON objects.

It should be able to look something like this:

[{"key":"key1","value":"A String"},{"key":"key2","value":{"InnerKey":"InnerValue"}}]

However, SQL seems to be forcing me to select either a string or an object.

SELECT
       [key] = kvp.[key],
       [value] = CASE
              WHEN ISJSON(kvp.[value]) = 1 THEN JSON_QUERY(kvp.[value])
              ELSE '"' + kvp.[value] + '"'  -- See note below
              END
FROM (VALUES
        ('key1', 'This value is a string')
       ,('key2', '{"description":"This value is an object"}')
       ,('key3', '["This","value","is","an","array","of","strings"]')
       ,('key4', NULL)

       -- Without these lines, the above 4 work fine; with either of them, even those 4 are broken
       --,('key5', (SELECT [description] = 'This value is a dynamic object' FOR JSON PATH, WITHOUT_ARRAY_WRAPPER))
       --,('key6', JSON_QUERY((SELECT [description] = 'This value is a dynamic object' FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)))
) AS kvp([key], [value])
FOR JSON PATH

Am I trying to do something that SQL can't support, or am I just missing the proper syntax for making this work?

*Note that the addition of the double-quotes seems like it shouldn't be necessary. But without those, SQL fails to wrap the string and generates bad JSON:

[{"key":"key1","value":This value is a string},...

Upvotes: 2

Views: 222

Answers (1)

CharlieNoTomatoes
CharlieNoTomatoes

Reputation: 521

If your query is modified to this, it works:

SELECT
   [key] = kvp.[key],
   [value] = ISNULL(
          JSON_QUERY(CASE WHEN ISJSON(kvp.[value]) = 1 THEN kvp.[value] END),
          '"' + STRING_ESCAPE(kvp.[value], 'json') + '"'
   )
FROM (VALUES
   ('key1', 'This value is a "string"')
   ,('key2', '{"description":"This value is an object"}')
   ,('key3', '["This","value","is","an","array","of","strings"]')
   ,('key4', NULL)

   -- These now work
   ,('key5', (SELECT [description] = 'This value is a dynamic object' FOR JSON PATH, WITHOUT_ARRAY_WRAPPER))
   ,('key6', JSON_QUERY((SELECT [description] = 'This value is a dynamic object' FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)))
) AS kvp([key], [value])
FOR JSON PATH, INCLUDE_NULL_VALUES

Of course, this wouldn't be sufficient if value was an int. Also, I can't really explain why yours doesn't work.

Upvotes: 2

Related Questions