Possible to Use Json Data in a Join Clause?

Question

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]}]

Any boolean expression is allowed. You would need to extract the fields you want to compare. — Gordon Linoff
– Gordon Linoff, Commented Jan 3, 2020 at 17:52
the json is on the Events Table (in a column called details). Brands and Items is where the information of the "item" lives, so the "id" is primary key of Items. — chobo2
– chobo2, Commented Jan 3, 2020 at 17:57

Zhorov · Accepted Answer · 2020-01-07 12:48:26Z

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

Fernando Veras · Accepted Answer · 2020-01-03 18:05:00Z

-1

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

answered Jan 3, 2020 at 18:05

Fernando Veras

5310 bronze badges

2 Comments

chobo2 Over a year ago

yes it's my own logic to insert, but I now need to get the details of the "items" now hence why It hink I need a join clause. I also may have "1" id or 100 ids to look up so I want to try to do it all in one go if possible hence why my where clause has an "in clause"

Fernando Veras Over a year ago

Let me see if I got it... Do you have the Items table that contains the items that are in the details column of the Event table, which in this case would be json's items but has nothing to do with the Event and Items table ID?

Collectives™ on Stack Overflow

Possible to Use Json Data in a Join Clause?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related