SQL Query for Json object inside Json

Question

I have the below Json string. I need to write a query to get the SP records. Without providing the index value, we need get the result.

 {
   "S": [
    {
      "Name": "Project1",
       "SP": [
        {
          "ID": 1,
          "Name": "Test1"
        },
        {
          "ID": 2,
          "Name": "Test2"
        },
  }]}

How do I query to get the SP values.

Expected Result:

 ID    Name 
 1     Test1 
 2     Test2

 I tried the below but not working. Can you please suggest the correct query.

SELECT DISTINCT JSON_VALUE(JsonData, '$.S[0].SP.ID') AS ID,
                JSON_VALUE(JsonData, '$.S[0].SP.Name') AS Name
    FROM TableA

which SQL engine are you using?

zealous
– zealous

2020-04-14 06:29:41 +00:00
Commented Apr 14, 2020 at 6:29 — zealous
– zealous, Commented Apr 14, 2020 at 6:29
I am using SQL Server 2019

Ashok Yaganti
– Ashok Yaganti

2020-04-14 06:49:10 +00:00
Commented Apr 14, 2020 at 6:49 — Ashok Yaganti
– Ashok Yaganti, Commented Apr 14, 2020 at 6:49

Barbaros Özhan · Accepted Answer · 2020-04-14 09:00:40Z

0

You can use JSON_QUERY nested in OPENJSON function containing WITH Clause in order to visit all members of SP array dynamically :

SELECT ID, Name
  FROM TableA
 CROSS APPLY OPENJSON(JSON_QUERY(JsonData, '$.S[0].SP'))
             WITH (ID   nvarchar(500) '$.ID',
                   Name nvarchar(500) '$.Name')

Btw, you need to fix the JsonData as converting to

{
   "S": [
    {
      "Name": "0219 Project Methodology - Allergies",
       "SP": [
        {
          "ID": 1,
          "Name": "Test1"
        },
        {
          "ID": 2,
          "Name": "Test2"
        }
  ] } ] }

Demo

answered Apr 14, 2020 at 9:00

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ashok Yaganti Over a year ago

How we can query, if there was one more json object inside SP?

Barbaros Özhan Over a year ago

Hi @AshokYaganti , Can you add this as a new question containig a sample JSON, please ?

Ashok Yaganti Over a year ago

Sure @Barbaros Ozhan

Ashok Yaganti Over a year ago

stackoverflow.com/questions/61268901/…

Collectives™ on Stack Overflow

SQL Query for Json object inside Json

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related