Multiple SQL columns into 1 json column

Question

I am new to querying and using JSON fields. I've done some research, but most of the info I find is regarding querying from JSON to rows and columns. My question is in opposite direction:

Let's say I have a table with 3 columns: (Age, Optin, City). Now i want to add one column in JSON format. I tried:

SELECT Age, Optin, City
INTO #JSON_Table
FROM MyTable
FOR JSON AUTO

Error message: Msg 13602, Level 16, State 1, Line 30 The FOR JSON clause is not allowed in a SELECT INTO statement.

Pretty basic stuff, I know, but I could use some assistence.

Nicklas Pouey-Winger · Accepted Answer · 2017-11-22 10:28:22Z

5

select * into #JSON_Table
from (SELECT Age, Optin, City
FROM MyTable
FOR JSON PATH
) a(X)

Where X is the column name

edited Nov 22, 2017 at 10:28

Nicklas Pouey-Winger

3,0337 gold badges48 silver badges76 bronze badges

answered Nov 22, 2017 at 9:18

Harshana

961 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

SQL_M Over a year ago

Thanks, I accepted this answer. One additional question: can I get the result such that for each person I get 1 row with the JSON column containing these 3 attributes?

Harshana Over a year ago

Can you elaborate more? Can you write a sample output json that you want?

SQL_M Over a year ago

I will try. :) ROW 1: PersonID 1, JSON_Field {Age:18, Optin:1, City:Rotterdam}. ROW 2 PersonID 2 JSON_Field {Age: 24, Optin: 0, City: New York}. Something like that I guess. Does that make sense?

Harshana Over a year ago

select * into #JSON_Table from (SELECT PersonID, Age [MyTable.Age], Optin [MyTable.Optin], City [MyTable.City] FROM MyTable FOR JSON PATH ) a(X)

SQL_M Over a year ago

Thanks again, this throws an error: invalid column names.

|

Ram Thilak · Accepted Answer · 2017-11-22 07:40:25Z

0

Try this query and also check with the sql server versions. some versions of sql like SQL SERVER 2012 doesn't supports this query and 2016 will does.

select * into #JSON_Table from (SELECT Age, Optin, City FROM MyTable FOR JSON PATH ) a

answered Nov 22, 2017 at 7:40

Ram Thilak

1

1 Comment

SQL_M Over a year ago

Thanks for your assistence. However, I get an error message: Msg 8155, Level 16, State 2, Line 29 No column name was specified for column 1 of 'J'. Msg 1038, Level 15, State 5, Line 25 An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

Collectives™ on Stack Overflow

Multiple SQL columns into 1 json column

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related