HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Extract Multidimensional JSON

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
jsonmultidimensionalextract

Problem

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.

Solution

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"
}]
}]
}

Code Snippets

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')

Context

StackExchange Database Administrators Q#135975, answer score: 2

Revisions (0)

No revisions yet.