SQL Server JSON для связи «многие ко многим»
У меня есть следующие таблицы (многие ко многим):
DROP TABLE IF EXISTS [dbo].[ItemOwner], [dbo].[Items], [dbo].[Owners]
GO
CREATE TABLE [dbo].[Items]
(
[Id] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[Name] [varchar](max) NOT NULL
);
CREATE TABLE [dbo].[Owners]
(
[Id] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[Name] [varchar](max) NOT NULL
);
CREATE TABLE [dbo].[ItemOwner]
(
[ItemId] [int] NOT NULL REFERENCES [dbo].[Items]([Id]),
[Ownerd] [int] NOT NULL REFERENCES [dbo].[Owners]([Id]),
UNIQUE([ItemId], [Ownerd])
);
INSERT INTO [dbo].[Items] ([Name])
VALUES ('item 1'), ('item 2'), ('item 3');;
INSERT INTO [dbo].[Owners] ([Name])
VALUES ('owner 1'), ('owner 2');
INSERT INTO [dbo].[ItemOwner]
VALUES (1, 1), (1, 2), -- the item is owned by two owners
(2, 1), (2, 2), -- the item is owned by two owners
(3, 1); -- the item is owned by one owner
Итак, если я это сделаю:
SELECT DISTINCT
STRING_AGG([Ownerd], ', ')
FROM
[dbo].[ItemOwner]
GROUP BY
[ItemId]
Я получаю все перестановки владельцев. Я хочу, чтобы эти перестановки были в объектах верхнего уровня и объединяли в них элементы.
Как я могу получить JSON в SQL Server, который выглядит следующим образом:
[
{
"owners": [
{
"id": 1,
"name": "owner 1"
},
{
"id": 2,
"name": "owner 2"
}
],
"own": [
{
"id": 1,
"name": "item 1"
},
{
"id": 2,
"name": "item 2"
}
]
},
{
"owners": [
{
"id": 1,
"name": "owner 1"
}
],
"own": [
{
"id": 3,
"name": "3"
}
]
}
]
Я перепробовал много вещей — понятия не имею, как совместить GROUP BY с правильной агрегатной функцией. SQL Server 2019 (значит, нет JSON_ARRAYAGG?)
Ответы 2
Вы можете использовать FOR JSON PATH например:
SELECT
(
SELECT
o.Id,
o.Name
FROM
[dbo].[Owners] o
JOIN
[dbo].[ItemOwner] io ON io.Ownerd = o.Id
GROUP BY
o.Id, o.Name
FOR JSON PATH
) AS owners,
(
SELECT
i.Id,
i.Name
FROM
[dbo].[Items] i
JOIN
[dbo].[ItemOwner] io ON io.ItemId = i.Id
JOIN
[dbo].[Owners] o ON io.Ownerd = o.Id
GROUP BY
o.Id, o.Name, i.Id, i.Name
FOR JSON PATH
) AS own FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
Вот документы Microsoft
Это не то, чего хотел ОП. ОП, похоже, хочет сгруппировать отдельные пары владелец/предмет.
Я знаю функцию FOR JSON PATH. Проблема в том, как получить ожидаемый результат ;-) Ваш запрос дает мне что-то другое.
Вам нужен способ получить отдельные группы одинаковых наборов владельцев/предметов. Для этого вам нужно сгруппировать не только отдельные OwnerId по ItemId, вам также нужно сгруппировать их по новому списку DistinctOwners и сгруппировать DistinctItems.
Затем вы можете использовать OPENJSON, чтобы открыть его и снова присоединиться к родительским таблицам.
К сожалению, SQL Server пока не поддерживает JSON_AGG, иначе все было бы проще. Вместо этого нам нужно использовать STRING_AGG и соединить с ним [].
WITH OwnersGrouped AS (
SELECT
io.ItemId,
'[' + STRING_AGG(io.OwnerId, ',') WITHIN GROUP (ORDER BY io.OwnerId) + ']' AS DistinctOwners
FROM
dbo.ItemOwner io
GROUP BY
io.ItemId
),
ItemsGrouped AS (
SELECT DISTINCT
'[' + STRING_AGG(og.ItemId, ',') + ']' AS DistinctItems,
og.DistinctOwners
FROM
OwnersGrouped og
GROUP BY
og.DistinctOwners
)
SELECT
'[' + STRING_AGG(j.json, ', ') + ']'
FROM ItemsGrouped ig
CROSS APPLY (
SELECT
(
SELECT
o.Id,
o.Name
FROM dbo.Owners o
JOIN OPENJSON(ig.DistinctOwners) d ON CAST(d.value AS int) = o.Id
FOR JSON PATH
) AS owners,
(
SELECT
i.Id,
i.Name
FROM dbo.Items i
JOIN OPENJSON(ig.DistinctItems) d ON CAST(d.value AS int) = i.Id
FOR JSON PATH
) AS own
FOR JSON PATH
) j(json);
Большое спасибо. Вопрос в том, есть ли способ обойти его «пошаговую» сборку и использовать массивы и приведения ;-)
Что-то не так с моим ответом? Что именно ты говоришь?
Результат нормальный, но я спрашивал, есть ли способ без обходных путей и приведения строк 😉
@damike НЕТ, ты НЕ спрашивал об этом, судя по тому, что я вижу здесь. Пожалуйста, просмотрите и процитируйте в своем вопросе, где вы это сделали, пожалуйста, если я здесь не прав. FWIW это дает именно то, что было задано в вопросе (конечно, без подробного форматирования вопросов; но JSON в этом не нуждается), только НЕБОЛЬШАЯ разница - «Id» против «id»
@damike Да, есть способы сделать это, используя сложное реляционное деление и треугольные перекрестные соединения. Сомневаюсь, что это будет быстрее.
@MarkSchultheiss, о чем ты говоришь?
@Charlieface Интересно, что не существует «чистого» решения SQL, в котором вам не придется иметь дело со строковыми массивами. Что такое «реляционное деление и треугольные перекрестные соединения»? ;-)
Что должно произойти, если также имеется строка (3, 2) «Предмет/владелец»? Ваша логика не слишком ясна