Select -> Json

Question

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

Answer 1

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.

Answer 2

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