SQL Server: implementing CROSS APPLY when filtering JSON data from a nested structure
This might be a silly question, but This might be a silly question, but This might be a silly question, but I'm trying to query a SQL Server 2019 database where I have a column containing JSON data. The JSON structure is nested, and I'm using `CROSS APPLY` to extract values inside it, but I'm running into issues where the query returns unexpected results or even no results at all. Here's a simplified version of my scenario: I have a table called `Orders` with a JSON column `OrderDetails`. Each `OrderDetails` entry looks something like this: ```json { "items": [ {"productId": 1, "quantity": 2}, {"productId": 2, "quantity": 1} ] } ``` I want to extract the `productId` and `quantity` from each item and return a flattened result set. My current SQL query looks like this: ```sql SELECT o.OrderID, i.value AS ProductId, JSON_VALUE(i.value, '$.quantity') AS Quantity FROM Orders o CROSS APPLY OPENJSON(o.OrderDetails, '$.items') AS i ``` However, I'm getting an behavior stating `want to invoke function JSON_VALUE on a NULL value` for some rows, and other times it simply returns an empty set. I've verified that the JSON is valid, but it seems like the `OPENJSON` function is failing to parse it correctly for certain rows. I've also tried checking if `i.value` is NULL before invoking `JSON_VALUE`, like this: ```sql SELECT o.OrderID, i.value AS ProductId, JSON_VALUE(i.value, '$.quantity') AS Quantity FROM Orders o CROSS APPLY OPENJSON(o.OrderDetails, '$.items') AS i WHERE i.value IS NOT NULL ``` Still, I face issues with rows that contain empty arrays or missing keys in the JSON structure. What is the best way to handle this situation, ensuring that the query works for all rows regardless of the JSON structure, and avoids NULL errors? Any insights or best practices for working with nested JSON in SQL Server would be greatly appreciated. For context: I'm using Sql on Linux. I'd really appreciate any guidance on this.