JSON, Please help creating the Json format from a table

Question

JSON, Please help creating the Json format from a table

PC238 20

Hello Everyone,

In SQL Server 2019 Database, I have a table with columns StudentId, Agreement, StudentName

Please help me with the query which creates below Jason for each record.

{   "SingleValues": [    {     "fieldValue": "No",     "fieldName": "Agreement"    
						 },    
						 {     "fieldValue": "John Doe",     "fieldName": "StudentName"    
						 }   
					]  
}

Yitzhak Khabinsky 27,116 Reputation points

2023-05-05T15:52:58.45+00:00

While asking a question you need to provide a minimal reproducible example:

(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.

(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.

(3) Desired output based on the sample data in the #1 above.

(4) Your SQL Server version (SELECT @@version;)
PC238 20 Reputation points

2023-05-05T17:24:07.7366667+00:00
Hello Yitzhak Khabinsky

Thanks for the reply.

I have a table with three columns StudentId, Agreement, StudentName.

and requesting a query for the json created as below.

{ "SingleValues": [ { "fieldValue": "No", "fieldName": "Agreement" }, { "fieldValue": "John Doe", "fieldName": "StudentName" } ] }

The sql server version is SQL Server 2019

Please advise what additional information you need

Regards,

PC
Yitzhak Khabinsky 27,116 Reputation points

2023-05-05T17:59:51.84+00:00

@PC238,

You can delete your comment. It is not helpful.

You need to edit your original question and provide ##1-4
Erland Sommarskog 133.9K Reputation points MVP Volunteer Moderator

2023-05-05T21:32:47.8733333+00:00
I think that the person who gave you this assignment may be pulling your legs. To wit, when I run:

DECLARE @json nvarchar(MAX) = N'{ "SingleValues": [ { "fieldValue": "No", "fieldName": "Agreement" }, { "fieldValue": "John Doe", "fieldName": "StudentName" } ] }' SELECT isjson(@json)

The return value is 0. So to construct this string, we cannot use the FOR JSON functions in SQL Server (since it is not a matter of creating JSON), but we would have to roll our own. Which is not that fun.
LiHongMSFT-4306 31,621 Reputation points

2023-05-09T02:27:00.8933333+00:00

Hi @PC238

Is this issue solved now? If you have any questions, please feel free to share with us.

And don't forget to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thanks for your understanding!

1 answer

Your answer

Yitzhak Khabinsky 27,116 Reputation points

2023-05-05T15:52:58.45+00:00

While asking a question you need to provide a minimal reproducible example:

(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.

(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.

(3) Desired output based on the sample data in the #1 above.

(4) Your SQL Server version (SELECT @@version;)
PC238 20 Reputation points

2023-05-05T17:24:07.7366667+00:00

Hello Yitzhak Khabinsky

Thanks for the reply.

I have a table with three columns StudentId, Agreement, StudentName.

and requesting a query for the json created as below.

{ "SingleValues": [ { "fieldValue": "No", "fieldName": "Agreement" }, { "fieldValue": "John Doe", "fieldName": "StudentName" } ] }

The sql server version is SQL Server 2019

Please advise what additional information you need

Regards,

PC
Yitzhak Khabinsky 27,116 Reputation points

2023-05-05T17:59:51.84+00:00

@PC238,

You can delete your comment. It is not helpful.

You need to edit your original question and provide ##1-4
Erland Sommarskog 133.9K Reputation points MVP Volunteer Moderator

2023-05-05T21:32:47.8733333+00:00

I think that the person who gave you this assignment may be pulling your legs. To wit, when I run:

DECLARE @json nvarchar(MAX) = N'{ "SingleValues": [ { "fieldValue": "No", "fieldName": "Agreement" }, { "fieldValue": "John Doe", "fieldName": "StudentName" } ] }' SELECT isjson(@json)

The return value is 0. So to construct this string, we cannot use the FOR JSON functions in SQL Server (since it is not a matter of creating JSON), but we would have to roll our own. Which is not that fun.
LiHongMSFT-4306 31,621 Reputation points

2023-05-09T02:27:00.8933333+00:00

Hi @PC238

Is this issue solved now? If you have any questions, please feel free to share with us.

And don't forget to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thanks for your understanding!

Answer 1

Hi @PC238

How about this query?

CREATE TABLE #TEMP(StudentId INT, Agreement VARCHAR(20), StudentName VARCHAR(20))
INSERT INTO #TEMP VALUES
(1,'No','John Doe')

SELECT *,CONCAT('{ "SingleValues": [ { "fieldValue": "'
               ,Agreement
			   ,'", "fieldName": "Agreement" },{ "fieldValue": "'
			   ,StudentName
			   ,'", "fieldName": "StudentName" } ] }') AS [JSON]
FROM #TEMP ;

Output:

User's image

Best regards,

Cosmog Hong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Share via

JSON, Please help creating the Json format from a table

1 answer

Your answer