SQL Server – How to Extract Multidimensional JSON

jsonsql serversql-server-2016

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.

Best 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"
        }]
    }]
}
Related Question