CODEWITHSUNDEEP
jsonsql-serverjoin
Chris
Chris

Reputation: 335

SQL Server JSON Join Many to Many

I am exploring the concept of using SQL Server to be more like a document database and in an average relational database model I would use a third table to join the many to many relationship between two objects say Products and Categories.

In SQL Server 2016 I have the following tables...

CREATE TABLE [dbo].[Products]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](250) NOT NULL,
    [Categories] [nvarchar](max) NULL

    CONSTRAINT [PK_Products] 
        PRIMARY KEY CLUSTERED ([ID] ASC)
)

CREATE TABLE [dbo].[Categories]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](250) NOT NULL,
    [ParentID] [int] NULL,

    CONSTRAINT [PK_Categories] 
        PRIMARY KEY CLUSTERED ([ID] ASC)
)

The Categories column in the Products table would hold a JSON array of IDs.

Is there a single query to select products and include a new column (JSON) of the related categories. CROSS APPLY duplicates each product row.

Something like this (but this doesn't work)...

SELECT 
    p.ID, p.Name,
    ((SELECT * FROM Categories 
      WHERE ID in (SELECT [value] FROM OPENJSON(p.Categories))) 
      FOR JSON PATH) AS Categories
FROM Products p

Some demo data

TRUNCATE TABLE Categories;

INSERT INTO Categories ([Name]) VALUES ('PC Systems');
INSERT INTO Categories ([Name], [ParentID]) VALUES ('Custom PC', 1);
INSERT INTO Categories ([Name], [ParentID]) VALUES ('Gaming PC', 1);
INSERT INTO Categories ([Name], [ParentID]) VALUES ('Small Form Factor', 1);
INSERT INTO Categories ([Name], [ParentID]) VALUES ('Business PCs', 1);
INSERT INTO Categories ([Name], [ParentID]) VALUES ('Mac', 1);

INSERT INTO Categories ([Name]) VALUES ('Speakers');
INSERT INTO Categories ([Name]) VALUES ('Monitors');

TRUNCATE TABLE Products;
DECLARE @jsonCategories NVARCHAR(MAX)

SET @jsonCategories = N'[7]';
INSERT INTO Products ([Name], [Categories]) VALUES ('HomePod', @jsonCategories);

SET @jsonCategories = N'[5, 4]';
INSERT INTO Products ([Name], [Categories]) VALUES ('Surface Pro', @jsonCategories);

Thanks for any help

Upvotes: 0

Views: 453

Answers (1)

Chris
Chris

Reputation: 335

Sorry, found my own answer

SELECT p.ID, p.Name,
    (
        SELECT * FROM Categories WHERE ID in (SELECT [value] FROM OPENJSON(p.Categories)) FOR JSON PATH
    ) as Categories
FROM Products p

Upvotes: 1

Related Questions