Can I convert a VARCHAR field body to unquoted JSON result in SQL Server 2016 CTP 2.X?

Question

I have downloaded and installed SQL Server 2016 Community Technology Preview 2.3, which if I understand correctly, should contain a built-in function called JSON_VALUE. (Update: CTP 2.4 has shipped and no mention is made of new JSON features yet, either)

Starting with the JSON features that DO already work, the "... FOR JSON AUTO" can be specified in my SELECT statement, like this:

SELECT [ID], [DATA] FROM [dbo].[JSONDEMO] FOR JSON AUTO

The result for my demo query above will be a resultset with one column, with some auto-generatedname like JSON_F123123123 and the value in JSON format, something like:

[{"ID":"IDVALUE", "DATA":"{ {name:'ramsoft', id:1234}, ... }", ... }] 

So far so good, but if DATA was containing JSON data, you would be receiving a JSON document containing a data string literal, even though that string literal is valid JSON. What you actually probably wanted, or what I wanted, rather, is:

 [{"ID":"IDVALUE", "DATA":{ {name:'ramsoft', id:1234}, ... }, ... }] 

According to some MS Blog posts, to get the JSON value as a JSON value instead of as a VARCHAR(n) string you should write:

SELECT [ID], JSON_VALUE(D.DATA,'$') DATA   
FROM [dbo].[JSONDEMO] D FOR JSON AUTO

This does not work, I get an error:

'JSON_VALUE' is not a recognized built-in function name.

Is some kind of JSON value extraction available in CTP 2.3, or is this not going to exist until CTP 3?

Answer 1

You'll need to wait for CTP 3 to use things like OPENJSON and JSON_VALUE. These constructs simply didn't ship in the CTPs up to and including CTP 2.4. That doesn't mean there isn't some way to get the result you want right now, but the amount of time and effort you might spend doing so is probably not worth it, especially if the functionality you need will be available in the next CTP, which should ship soon.