Question :
I have some tables:
teste // id, descricao
teste2 // id, descricao
relacao // id, idTeste, idTeste2
With this structure, I got all products owned by a client. Now I’m trying to use FOR JSON PATH
to extract data from the database:
This is where I am at the moment:
SELECT
teste2.descricao AS "nome",
relacao.id AS "produto.id"
FROM teste2
INNER JOIN relacao ON relacao.idTeste2 = teste2.id
FOR JSON PATH
And this is the result from the server:
[{
"nome": "Raphael Schubert",
"produto": {
"id": 1
}
}, {
"nome": "Raphael Schubert",
"produto": {
"id": 2
}
}, {
"nome": "Lorraine Schubert",
"produto": {
"id": 3
}
}, {
"nome": "Lorraine Schubert",
"produto": {
"id": 4
}
}]
I was expecting to create a query where the answer is like this:
[{
"nome": "Raphael Schubert",
"produto": [{
"id": 1
}, {
"id": 2
}]
}, {
"nome": "Lorraine Schubert",
"produto": [{
"id": 3
}, {
"id": 4
}]
}]
I think with for each at select was possible, but I’m new to SQL Server, I know a bit about MySQL but was not able to extract that info.
Answer :
As Martin Smith suggested in a comment the necessary syntax is very similar to FOR XML
. Here is how I solved my problem:
SELECT
teste2.descricao AS cliente,
teste2.id AS clienteID,
(
SELECT
teste.id,
teste.descricao AS produto
FROM relacao
INNER JOIN teste ON relacao.idTeste = teste.id
WHERE relacao.idTeste2 = teste2.id
FOR JSON PATH
) AS produtos
FROM teste2
FOR JSON PATH, ROOT('clientes')
Result:
{
"clientes": [{
"cliente": "Raphael Schubert",
"clienteID": 1,
"produtos": [{
"id": 1,
"produto": "Casa"
}, {
"id": 2,
"produto": "Carro"
}]
}, {
"cliente": "Lorraine Schubert",
"clienteID": 2,
"produtos": [{
"id": 3,
"produto": "Moto"
}, {
"id": 1,
"produto": "Casa"
}]
}]
}