T-SQL OpenJson Nested Array

Question

Iam Struggling with following JSOn Structure

Declare @Json Nvarchar(max)
Set @Json = '
     {
  "entities": [
    {
      "Fields": [
        {
          "Name": "test-id",
          "values": [
            {
              "value": "1851"
            }
          ]
        },
        {
          "Name": "test-name",
          "values": [
            {
              "value": "01_DUMMY"
            }
          ]
        }


      ],
      "Type": "run",
      "children-count": 0
    },
    {
      "Fields": [
        {
          "Name": "test-id",
          "values": [
            {
              "value": "1852"
            }
          ]
        },
        {
          "Name": "test-name",
          "values": [
            {
              "value": "02_DUMMY"
            }
          ]
        }
      ],
      "Type": "run",
      "children-count": 0
    }
  ],
  "TotalResults": 2
}'

My Output should look like this:

    test-id|test-name|Type|Children-count
    1851   |01_DUMMY |run |0
    1852   |02_DUMMY |run |0

I tried to use the Examples posted here but none is matching my Needs.

My closest apporach was this T-SQL Syntax

Select
*
From OPENJSON (@JSON,N'$.entities') E
CROSS APPLY OPENJSON (E.[value]) F 
CROSS APPLY OPENJSON (F.[value],'$') V where F.type = 4

My next idea was to use this SQL CODE to open the next nested Array but iam always getting an error msg(

Lookup Error - SQL Server Database Error: Incorrect syntax near the keyword 'CROSS'.

)

Select
*
From OPENJSON (@JSON,N'$.entities') E
CROSS APPLY OPENJSON (E.[value]) F 
CROSS APPLY OPENJSON (F.[value]) V where F.type = 4 
CROSS APPLY OPENJSON (V.[value]) N

Iam not sure how to get Closer to my needed Output. To be honest I just started with T-SQL and never worked before with JSON Files.

Regards Johann

Gottfried Lesigang · Accepted Answer · 2019-03-12 11:10:38Z

This is rather deeply nested. I think, you've got the right idea to dive deeper and deeper using a serie of OPENJSON. Try it like this to get your values:

Declare @Json Nvarchar(max)
Set @Json = '
     {
  "entities": [
    {
      "Fields": [
        {
          "Name": "test-id",
          "values": [
            {
              "value": "1851"
            }
          ]
        },
        {
          "Name": "test-name",
          "values": [
            {
              "value": "01_DUMMY"
            }
          ]
        }


      ],
      "Type": "run",
      "children-count": 0
    },
    {
      "Fields": [
        {
          "Name": "test-id",
          "values": [
            {
              "value": "1852"
            }
          ]
        },
        {
          "Name": "test-name",
          "values": [
            {
              "value": "02_DUMMY"
            }
          ]
        }
      ],
      "Type": "run",
      "children-count": 0
    }
  ],
  "TotalResults": 2
}';

--This is the query

WITH ReadJson AS
(
    SELECT A.TotalResults
          ,C.[Type]
          ,C.[children-count]
          ,D.[Name]
          ,E.*
    FROM OPENJSON(@Json) 
    WITH(TotalResults INT, entities NVARCHAR(MAX) AS JSON) A
    CROSS APPLY OPENJSON(A.entities) B
    CROSS APPLY OPENJSON(B.[value])
    WITH(Fields NVARCHAR(MAX) AS JSON,[Type] VARCHAR(100),[children-count] INT) C
    CROSS APPLY OPENJSON(C.Fields) 
    WITH([Name] VARCHAR(100),[values] NVARCHAR(MAX) AS JSON) D
    CROSS APPLY OPENJSON(D.[values]) 
    WITH([value] VARCHAR(100)) E
)
SELECT * FROM ReadJson;

The result

+---+-----+---+-----------+----------+
| 2 | run | 0 | test-id   | 1851     |
+---+-----+---+-----------+----------+
| 2 | run | 0 | test-name | 01_DUMMY |
+---+-----+---+-----------+----------+
| 2 | run | 0 | test-id   | 1852     |
+---+-----+---+-----------+----------+
| 2 | run | 0 | test-name | 02_DUMMY |
+---+-----+---+-----------+----------+

Do you think you can manage the rest?

Hey Shnugu, Thank you for your answer, i will take your answer and try to complete it on my own.

Collectives™ on Stack Overflow

T-SQL OpenJson Nested Array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related