Microsoft SQL Server Queries with JSON data JOIN table with inner JSON collection
Example
If you have a "child table" formatted as JSON collection and stored in-row as JSON column, you can unpack this collection, transform it to table and join it with parent row. Instead of the standard JOIN operator, you should use CROSS APPLY. In this example, product parts are formatted as collection of JSON objects in and stored in Data column:
select ProductID, Name, Size, Price, Quantity, PartName, Code
from Product
CROSS APPLY OPENJSON(Data, '$.Parts') WITH (PartName varchar(20), Code varchar(5))
Result of the query is equivalent to the join between Product and Part tables.
