Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

SQL Server JSON for many-to-many

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

Tags:

2

Answers


  1. 0

    You can use FOR JSON PATH like:

    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

    Here are the Microsoft docs

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search