Com o esquema de banco de dados a seguir, estou tentando recuperar um JSON dos itens combinados com propriedades e um indicador se eles estiverem conectados.
Comecei com:
DECLARE @Item TABLE
(
ItemId INT,
Name VARCHAR(25)
);
INSERT INTO @Item
(
ItemId,
Name
)
VALUES
(
1,
'Item 1'
),
(
2,
'Item 2'
);
DECLARE @Property TABLE
(
PropertyId INT,
Name VARCHAR(10)
);
INSERT INTO @Property
(
PropertyId,
Name
)
VALUES
(
1,
'Property 1'
),
(
2,
'Property 2'
),
(
3,
'Property 3'
);
DECLARE @ItemProperty TABLE
(
ItemId INT,
PropertyId INT
);
INSERT INTO @ItemProperty
(
ItemId,
PropertyId
)
VALUES
(
1,
1
),
(
1,
2
),
(
2,
2
),
(
2,
3
);
SELECT *
FROM @Item AS i
OUTER APPLY (
SELECT
p.Name,
CASE WHEN ip.PropertyId IS NOT NULL THEN
CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END AS ItemExists
FROM @Property AS p
LEFT JOIN @ItemProperty AS ip ON ip.PropertyId = p.PropertyId
AND ip.ItemId = i.ItemId
) a
WHERE i.ItemId = 1
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER;
Isso me dá o seguinte resultado:
{
"ItemId": 1,
"Name": "Item 1",
"a": [
{
"Name": "Property 1",
"ItemExists": true
},
{
"Name": "Property 2",
"ItemExists": true
},
{
"Name": "Property 3",
"ItemExists": false
}
]
}
No entanto, eu gostaria de ter isso como resultado
{
"ItemId": 1,
"Name": "Item 1",
"Property 1": true,
"Property 2": true,
"Property 3": false
}
E quando adiciono uma nova propriedade a @Property, gostaria que ela fosse adicionada automaticamente.
Como posso conseguir isso?
Infelizmente, o SQL Server não oferece suporte no momento
JSON_OBJECT_AGG
, o que tornaria esse tipo de consulta significativamente mais fácil.Em vez disso, você precisa construir o JSON manualmente usando
STRING_AGG
eSTRING_ESCAPE
banco de dados<> violino
Uma maneira (espero que esteja certa), porque você tem um número desconhecido de "Propriedade x", é usar o pivot dinâmico. Veja aqui mais sobre isso. https://www.mssqltips.com/sqlservertip/6245/sql-server-dynamic-pivot-query/
Armazenei o conjunto de resultados em uma tabela #tmp
Então, com base no pivô dinâmico:
A saída será:
Esta é a versão do código rígido
dbfiddle aqui
sem pivô...
dbfiddle aqui