Microsoft SQL Server cross apply Join table rows with JSON array stored in cell
Example
CROSS APPLY enables you to "join" rows from a table with collection of JSON objects stored in a column.
Imagine that you have a Company table with a column that contains an array of products (ProductList column) formatted as JSON array. OPENJSON table value function can parse these values and return the set of products. You can select all rows from a Company table, parse JSON products with OPENJSON and "join" generated results with parent Company row:
SELECT *
FROM Companies c
CROSS APPLY OPENJSON( c.ProductList )
WITH ( Id int, Title nvarchar(30), Price money)
For each row, value of ProductList cell will be provided to OPENJSON function that will transform JSON objects to rows with the schema defined in WITH clause.
