👀 Views: 69 💬 Answers: 1 📅 Created: 2025-06-12

sql-server json openjson sql-server-2019 SQL

I'm building a feature where I'm sure I'm missing something obvious here, but I'm prototyping a solution and Can someone help me understand I tried several approaches but none seem to work... This might be a silly question, but I'm working with SQL Server 2019 and trying to parse a JSON string that contains nested arrays using `OPENJSON`. The JSON structure looks like this: ```json { "students": [ { "name": "Alice", "grades": [ {"subject": "Math", "score": 90}, {"subject": "Science", "score": 85} ] }, { "name": "Bob", "grades": [ {"subject": "Math", "score": 75}, {"subject": "Science", "score": 80} ] } ] } ``` I want to extract each student's name along with their grades into separate rows. Initially, I tried using the following query: ```sql SELECT * FROM OPENJSON(@json) WITH ( name NVARCHAR(100) '$.name', grades NVARCHAR(MAX) AS JSON '$.grades' ) AS Students ``` However, this returned an behavior saying "Invalid column name 'grades'" when trying to access the nested array. I realized that `OPENJSON` does not directly handle nested arrays in this way, so I attempted to flatten it with another `OPENJSON` call: ```sql SELECT name, subject, score FROM OPENJSON(@json, '$.students') WITH ( name NVARCHAR(100) '$.name', grades NVARCHAR(MAX) AS JSON '$.grades' ) AS Students CROSS APPLY OPENJSON(grades) WITH ( subject NVARCHAR(100) '$.subject', score INT '$.score' ) AS Grades ``` This query runs fine, but it doesn't return any rows due to the way I'm referencing the `grades` field. I've also tried different combinations of JSON paths and structures, but I keep hitting a wall. What am I doing wrong? How can I correctly extract data from nested JSON arrays in this scenario? How would you solve this? Am I missing something obvious? I'm working in a Windows 11 environment. I appreciate any insights! Any suggestions would be helpful. This is happening in both development and production on Debian. Any feedback is welcome! This is for a microservice running on macOS.