Sql-server – Select multiple columns as JSON in a single query

jsonsql server

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 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.

Best Answer

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.

Related Question