How to parse JSON array string by using mssql?

Question

I've had created the function ParseJson below :

    create function ParseJson(
    @json nvarchar(max))
returns @tempTable table (topKey nvarchar(max), [Key] nvarchar(max), [Value] nvarchar(max))
as
begin

    insert 
        @tempTable
    select 
        x.[key] topKey
        , y.[key] 
        , y.[value] 
    from openjson(@json) x
    cross apply openjson(x.[value]) y

    return

end

-- execute
select * from ParseJson(@json)

I defined a variable @json which is not an array string :

set @json = 
N'{
  "Chapter":
  {
      "Section":"1.1"
      , "Title":"Hello world."
  }
}'

Then I execute the query I will get the result :

If I reset the variable @json which is an array string :

declare @json nvarchar(max)
set @json = 
N'{
  "Chapter":[
        {
            "Section":"1.1"
            , "Title":"Hello world."
        }
        ,
        {
            "Section":"1.2"
            , "Title":"Be happy."
        }       
    ]
}'

Then I execute the query I will get the result :

I hope I can get the result below :

I don't know the result that I expectation is reasonable?

How can I adjust the function to meet the expectation?

Please what is your SQL Server version ? if your're a version greater than (2016) take look at thoses post : learn.microsoft.com/en-us/sql/relational-databases/json/… ; learn.microsoft.com/en-us/sql/relational-databases/json/…; stackoverflow.com/questions/2867501/parse-json-in-tsql — Sanpas
– Sanpas, Commented Mar 4, 2019 at 11:09
While you can try to capture the logic in a "general" function, you would end up with a bunch of special casing for checking the structure before parsing that makes it not general after all. It makes more sense to parse JSON on an individual basis per query, depending on what's needed (for example, SELECT * FROM OPENJSON(@json, '$.Chapter') WITH (Section NVARCHAR(100), Title NVARCHAR(100)), which works for both the singleton and array case). — Jeroen Mostert
– Jeroen Mostert, Commented Mar 4, 2019 at 11:18

Zhorov · Accepted Answer · 2019-03-06 06:51:13Z

1

If I understand your question correctly, next statement is one possible approach to get your results. It's an example that shows how to get array keys and values (I've added additional Something key). What you need is one additional CROSS APPLY.

DECLARE @json nvarchar(max)
SET @json = 
N'{
  "Chapter":[
        {
            "Section":"1.1", 
            "Title":"Hello world.",
            "Something":"Something value"
        }
        ,
        {
            "Section":"1.2", 
            "Title":"Be happy."
        }       
    ]
}'

SELECT 
   x.[key] topKey, 
   z.[key],
   z.[value]
FROM OPENJSON(@json) x
CROSS APPLY (SELECT * FROM OPENJSON(x.[value])) y
CROSS APPLY (SELECT * FROM OPENJSON(y.[value])) z

Output:

topKey  key         value
Chapter Section     1.1
Chapter Title       Hello world.
Chapter Something   Something value
Chapter Section     1.2
Chapter Title       Be happy.

Update:

If your JSON has different types of nested objects, approach will be different:

DECLARE @json nvarchar(max)
set @json = N'{ 
   "Chapter": [ 
       {
       "Section":"1.1", 
       "Title":"Hello world.", 
       "Something":"Something value"
       }, 
       {
       "Section":"1.2", 
       "Title":"Be happy." 
       }
   ], 
   "Author": { "name":"Annie" , "sex":"Female" } 
}'

SELECT 
   x.[key] topKey,
   z.[key] [Key],
   z.[value] AS [Value]
FROM OPENJSON(@json) x
CROSS APPLY (SELECT * FROM OPENJSON(x.[value])) y
CROSS APPLY (SELECT * FROM OPENJSON(y.[value])) z
WHERE y.[type] = 5
UNION ALL
SELECT 
   x.[key] topKey,
   y.[key] [Key],
   y.[value] AS [Value]
FROM OPENJSON(@json) x
CROSS APPLY (SELECT * FROM OPENJSON(x.[value])) y
WHERE y.[type] <> 5

Output:

topKey  Key         Value
Chapter Section     1.1
Chapter Title       Hello world.
Chapter Something   Something value
Chapter Section     1.2
Chapter Title       Be happy.
Author  name        Annie
Author  sex         Female

edited Mar 6, 2019 at 6:51

answered Mar 4, 2019 at 11:32

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

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

2 Comments

Annie Over a year ago

Thanks! But if I

set @json = set @json =  N'{      "Chapter":[         {            "Section":"1.1",          "Title":"Hello world.",          "Something":"Something value"       }       , {            "Section":"1.2",          "Title":"Be happy."       }    ]    , "Author":{         "name":"Annie"       , "sex":"Female"    } }'

I will get the error message : The JSON text format is incorrect. Unexpected character 'A' was found at position 0.

Zhorov Over a year ago

@Annie @Annie yes, because you have JSON array and JSON text for Chapter and Author. If this is your case, approach must be different for Chapter and Author. Check updated answer.

Collectives™ on Stack Overflow

How to parse JSON array string by using mssql?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related