Select multiple columns as JSON in a single query

Question

How can I combine several columns (of varying types, e.g. int, datetime, nvarchar) as a single JSON-formatted expression in a select query? For example, I have columns col1, col2 and want the query to return a single column where each row looks like this:

{col1: val1, col2: val2}

Can I use FOR JSON? I'm using SQL Server 2017, but the database I'm querying is 2008 (100) compatibility mode.

Answer 1

All the JSON syntax works regardless of compatibility level.

CREATE TABLE dbo.x(col1 char(4), col2 char(4));
GO
INSERT dbo.x(col1, col2) VALUES('val1','val2');
GO
SELECT * FROM dbo.x FOR JSON AUTO;

Results:

[{"col1":"val1","col2":"val2"}]

If you need it without the square brackets, you'll need to abstract it away so you can assign a column name to the JSON output:

;WITH x(y) AS (SELECT * FROM dbo.x FOR JSON AUTO)
 SELECT PARSENAME(y,1) FROM x;

Results:

{"col1":"val1","col2":"val2"}

And without quotes:

;WITH x(y) AS (SELECT * FROM dbo.x FOR JSON AUTO)
 SELECT REPLACE(PARSENAME(y,1),'"',' ') FROM x;

Results:

{ col1 : val1 , col2 : val2 }

And yes it works with all types:

DROP TABLE dbo.x;
GO
CREATE TABLE dbo.x(col1 char(4), col2 char(4), col3 int, col4 date);
GO
INSERT dbo.x(col1,col2,col3,col4) VALUES('val1', 'val2', 5, GETDATE());
GO
SELECT * FROM dbo.x FOR JSON AUTO;

Results:

[{"col1":"val1","col2":"val2","col3":5,"col4":"2019-02-11"}]

This assumes you don't have the closing square bracket ] in your data. If you do, you'll either need to double it on extraction, before FOR JSON looks at it, or else use a more tedious method than PARSENAME() to removing the outer square brackets.

• Yury: This is great, thank you. Can I also select other columns (not JSON) alongside the JSON one? It seems that the FOR AUTO applies to the whole query, right? I guess it would need to be some sort of join.