0

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.

New contributor
Yury is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
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.

  • 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. – Yury Feb 11 at 20:12

Your Answer

Yury is a new contributor. Be nice, and check out our Code of Conduct.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.