How to look up for a key value pair in JSON object using SQL?

Question

Normally, in most of the json objects the location path for the key is fixed. So, the path is specified in the OPENJSON method, the value is extracted out accordingly. But I'm encountering a case when the path is not fixed and the json object is dynamic. The location of certain keys change.

For example the code below is a sample JSON:

Declare @Jsonobj as nvarchar(max)
Select @Jsonobj = N'{
    "ID": "StudentInformation",
    "Name": "Student Information",
    "Type": "s_info",
    "Details": [
        "Student Information",
        "Greendale Community College"
    ],
    "Date": "21 October 2021",
    "Rows": [
        {
            "RowType": "Header",
            "Cells": [
                {
                    "Value": ""
                },
                {
                    "Value": "21 Feb 2021"
                },
                {
                    "Value": "22 Aug 2020"
                }
            ]
        },
        {
            "RowType": "Section",
            "Title": "Class",
            "Rows": []
        },
        {
            "RowType": "Section",
            "Title": "Grade",
            "Rows": [
                {
                    "RowType": "Row",
                    "Cells": [
                        {
                            "Value": "5A",
                            "Property": [
                                {
                                    "Id": "1",
                                    "Value": "John Smith"
                                }
                            ]
                        },
                        {
                            "Value": "5A",
                            "Property": [
                                {
                                    "Id": "2",
                                    "Value": "Jane Doe"
                                }
                            ]
                        },
                        {
                            "Value": "5B",
                            "Property": [
                                {
                                    "Id": "1",
                                    "Value": "Ben Frank"
                                }
                            ]
                        }
                    ]
                }
            ]
        }
    ]
}'

There is an instance of the same keys like Rows, Cells or other JSON values That occurs multiple times while the presence of the key named "Property" is something which is dynamic, it can be in either places throughout the json objects. Normally we would use a path based approach as we know that at certain path we'll find the key value pair. Below is the sample SQL that extracts out the name out of the property KVP from JSON above.

SELECT JSON_VALUE(v.value, 'strict $.Value') as Names
FROM OPENJSON(@Jsonobj, 'strict $.Rows[2].Rows') c
CROSS APPLY OPENJSON(c.value, 'strict $.Cells') p
CROSS APPLY OPENJSON(p.value, 'strict $.Property') v

Is there a much simpler way to do so without depending on the path or using CROSS APPLY OPENJSON too many times?

HERE'S THE OUTPUT:

Names
John Smith
Jane Doe
Ben Frank

Answer 1

Hi @Nikul Vyas ,

Please have a try with below query which could help find out all value of Property under the Rows object and you could filter out all null value so that you do not have to define the fixed location of Property.

SELECT d.value Names  
FROM OPENJSON(@Jsonobj,'$.Rows')  
WITH([Rows] NVARCHAR(MAX) AS JSON) A  
OUTER APPLY OPENJSON(A.[Rows]) B  
OUTER APPLY OPENJSON(B.[value],'$.Cells') C  
OUTER APPLY OPENJSON(c.[value],'$.Property') WITH (Value NVARCHAR(MAX)) d  
where d.value is not null  

Output:

Names  
John Smith  
Jane Doe  
Ben Frank  

Best regards,
Melissa

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.