👀 Views: 76 💬 Answers: 1 📅 Created: 2025-06-05

sql-server json cross-apply sql

I'm working with a question with a query that uses `CROSS APPLY` to parse JSON data from a column. The intention is to extract values from a JSON string stored in a table, but I'm getting unexpected NULL values in the result set. The table structure is as follows: ```sql CREATE TABLE Orders ( OrderID INT PRIMARY KEY, OrderDetails NVARCHAR(MAX) ); ``` The `OrderDetails` column contains JSON data, like this: ```json {"Items":[{"ProductID":1,"Quantity":2},{"ProductID":2,"Quantity":1}]} ``` Here’s the query I'm trying to run: ```sql SELECT o.OrderID, i.ProductID, i.Quantity FROM Orders o CROSS APPLY OPENJSON(o.OrderDetails, '$.Items') AS i WITH ( ProductID INT '$.ProductID', Quantity INT '$.Quantity' ); ``` This should return the `OrderID`, `ProductID`, and `Quantity` for each item in the JSON array. However, for some records, I get NULL values for `ProductID` and `Quantity`. After checking the JSON data format, I verified that the relevant records indeed have the correct structure. I also made sure that `OPENJSON` is properly parsing the JSON path. I tried to replace the `CROSS APPLY` with a simple `SELECT` to see if the question continues, and it does not, suggesting that the scenario is isolated to how `CROSS APPLY` interacts with `OPENJSON`. I’ve also verified that the SQL Server version is 15.0. It’s unclear why this behavior is happening, especially since the data appears to be valid. Any insights on why I might be getting NULLs in this case would be greatly appreciated!