SQL Server JSON for many-to-many

Question

I have the following tables (many-to-many):

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

So if I do:

SELECT DISTINCT
    STRING_AGG([Ownerd], ', ')
FROM
    [dbo].[ItemOwner]
GROUP BY 
    [ItemId]

I get all permutations of owners. I want these permutations at the top level objects and aggregate the items to it.

How can I get a JSON in SQL Server which looks like this:

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

I tried a lot of things - I have no clue how to combine a GROUP BY with an proper aggregate function. SQL Server 2019 (so no JSON_ARRAYAGG?)

Answer 1

What you are looking for is a way to get distinct groups of identical owner/item sets. To do this, you need to group up not just the distinct OwnerId by the ItemId, you also need to group that by the new DistinctOwners list and get the grouped up DistinctItems.

Then you can use OPENJSON to crack that back open and join back to the parent tables.

Unfortunately, SQL Server does not support JSON_AGG yet, otherwise this would have been simpler. Instead we need to use STRING_AGG and concat the [] to it.

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

db<>fiddle