question

NikulVyas-3551 avatar image
0 Votes"
NikulVyas-3551 asked NikulVyas-5311 edited

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

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

sql-server-transact-sqlsql-server-integration-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi NikulVyas-3551,

Could you please provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·

1 Answer

MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered NikulVyas-5311 edited

Hi @NikulVyas-3551,

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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MelissaMa-msft ,

Thank you for the answer the solution is working however, it's targeting "Rows", o a property need to have a value which is correct. So, if I want to build a recursive look up for any key value pair in the @JsonObj I need to add the column/object in the With brackets?

For example, if I'd like to associate the relevant title next to the names I'm trying this:

  SELECT  JSON_VALUE(A.[Title],'$.Title'),d.value
  FROM OPENJSON(@Jsonobj,'$.Rows') WITH([Rows] NVARCHAR(MAX) AS JSON,
                                         [Title] 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

But seems like I'm not able to understand the concept properly, Can you please guide me through this & additionally point me to the correct place where I can find some Microsoft documentation on Type of "APPLY" on OPENJSON?

Here's the output it's should have:

 Title         Names
 Grade    John Smith
 Grade    Jane Doe
 Grade    Ben Frank





0 Votes 0 ·

Hi @NikulVyas-3551,

I'm afraid that I have limited knowledge with complex JSON data processing.

You could refer to some examples in openjson and check whether it is helpful.

If you still face any concern or issue with updated requirement, you could post a new question and you will get more professional help from other experts.

Thanks for your understanding.

Best regards,
Melissa

1 Vote 1 ·

Awesome! actually the documentation that you sent helped me figure out the proper structure that I needed to have.

Posting the look up query which gives the desired new result. Thank you @MelissaMa-msft once again for the guidance.

  SELECT  A.[Title],
  d.value
  FROM OPENJSON(@Jsonobj,'$.Rows') 
     
  WITH([Rows] NVARCHAR(MAX) '$.Rows' AS JSON,
         [Title] NVARCHAR(MAX) '$.Title') 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
0 Votes 0 ·