question

ANB avatar image
0 Votes"
ANB asked EchoLiu-msft commented

Select -> Json

I have 2 tables:

a) Employees

EmployeeId: 1
EmployeeName: Maria

EmployeeId: 2
EmployeeName: Jose

EmployeeId: 3
EmployeeName: Junior


b) Products
Id: 1
ProductName: Book
EmployeeId: 1

Id: 2
ProductName: Pen
EmployeeId: 2

Id: 3
ProductName: Box
EmployeeId: 1


So I would like to have something like:
EmployeeId: 1, EmployeeName: Maria, Products: Book, Pen

But I will need convert it to Json to have something like:
{
employeeId: 1,
employeeName: Maria,
products {
productName: Book,
productName: Box
}
}

Whats the best way to do it ?
Thx

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered

Your example does not seem to be a valid JSON.

Check the JSON output of this query:

 select e.*, products.ProductName
 from Employees e
 left join Products products on products.EmployeeId = e.EmployeeId
 for json auto


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @AgaNeto-3704,

Please refer to:

 CREATE TABLE Employees(EmployeeId INT,EmployeeName VARCHAR(15))
 INSERT INTO Employees VALUES(1,'Maria'),(2,'Jose'),(3,'Junior')
    
 CREATE TABLE Products(ID INT,ProductName VARCHAR(15),EmployeeId INT)
 INSERT INTO Products VALUES(1,'Book',1),(2,'Pen',2),(3,'Box',1)
    
 SELECT REPLACE(REPLACE(REPLACE((SELECT e.*,Products.ProductName
 FROM Employees e
 JOIN Products Products ON Products.EmployeeId = e.EmployeeId
 WHERE e.EmployeeId=1
 FOR JSON AUTO),'"',''),'[',''),']','')

Output:

 {EmployeeId:1,
 EmployeeName:Maria,
 Products:{ProductName:Book},{ProductName:Box}}

If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.
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.




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.