Create View and For Json clause

Question

With SQL Server 2016, I can run this simple query:

SELECT  colA, colB, json_query(infoJson) AS Expr1
FROM    dbo.Table_1 
FOR     json auto

I can even wrap it in a stored procedure. And it runs nicely.

But when I tried to create a view out of that statement, something weird (at least to me):

CREATE VIEW [dbo].[View_1]
AS
SELECT   colA, colB, json_query(infoJson) AS Expr1
FROM     dbo.Table_1 
FOR      json auto

This is the error I get:

Msg 4511, Level 16, State 1, Procedure View_1, Line 5 [Batch Start Line 9] Create View or Function failed because no column name was specified for column 1.

But as soon as I comment --for json auto, the VIEW is created as expected.

Why? I'd really like it to have have FOR JSON in my sql view.

Answer 1

FOR XML | JSON AUTO returns a single column (character LOB). So you need to modify the SELECT statement to return a single column in the view like:

CREATE VIEW [dbo].[View_1]
AS
SELECT (
SELECT   colA, colB, json_query(infoJson) AS Expr1
FROM     dbo.Table_1 
FOR      json auto
) as X