Possible to Use Json Data in a Join Clause?

I have some tracking data and since some of the data changes between "events" I am storing it as a JSON in a column in SQL Server.

I have this

{"items":[{"ids":[51130]}

Now this has the item id. I now want to get the information of the "item" but I am not sure how to do this as I would need some joins.

If I was doing this with regular joins I would have something like this

SELECT        *
FROM            Brands INNER JOIN
                         Items ON Brands.Id = Items.BrandId
						 Events ON Events.ItemId = Items.Id
			     where Items.Id in (51130)

Sample Data

Brands
1   Apple
2   Samsung

Items
id  name description brandId
51130 Galaxy 10  "Smartphone"  2

Event
id Details 
1  {"items":[{"ids":[51130]}]

I hope I understand this correctly. You need to parse the JSON, stored in details column and return the content as a table using OPENJSON(). The next example demonstrates how to use OPENJSON() with one table (note, that the JSON in the question is not correct):

Table:

CREATE TABLE Events (
   id int, 
   details nvarchar(max)
)
INSERT INTO Events (id, details)
VALUES (1, N'{"items":[{"ids":[51130]}]')

Statement:

SELECT id, itemId
FROM Events
CROSS APPLY OPENJSON(details, '$.items[0].ids') WITH (itemId int '$') 

Result:

----------
id	itemId
----------
1	51130

For complex statements, you may try to use the appropriate joins to get the expected results.

Tables:

CREATE TABLE Brands (
   brandId int,
   brandName nvarchar(100)
)
INSERT INTO Brands
   (BrandId, BrandName)
VALUES
   (1, N'Apple'),
   (2, N'Samsung')
CREATE TABLE Items (
   id int,
   name nvarchar(50),
   description nvarchar(50),
   brandId int
)   
INSERT INTO Items
   (id, name, description, brandId)
VALUES
   (51130, N'Galaxy 10', N'Smartphone', 2)
CREATE TABLE Events (
   id int, 
   details nvarchar(max)
)
INSERT INTO Events 
   (id, details)
VALUES
   (1, N'{"items":[{"ids":[51130]}]')

Statement:

SELECT *
FROM Brands b
INNER JOIN Items i ON b.brandId = i.BrandId
INNER JOIN (
   SELECT id, itemId
   FROM Events
   CROSS APPLY OPENJSON(details, '$.items[0].ids') WITH (itemId int '$') 
) e ON i.id = e.itemId

Result:

----------------------------------------------------------------------
brandId	brandName	id	    name	    description	brandId	id	itemId
----------------------------------------------------------------------
2	    Samsung	    51130	Galaxy 10	Smartphone	2	    1	51130

For begin, i suppose you use sql's own logic to insert json.

To retrieve the data you want, must use this function

JSON_VALUE("yourjsoncolumn", '$.yourjson.atribute') = 51130

In this article have full explanation to resolve your problem.

https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15