CODEWITHSUNDEEP
jsonsql-server-2016
Annie
Annie

Reputation: 149

How to parse JSON array string by using mssql?

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 :

result_single

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 : result_array

I hope I can get the result below :

result

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

How can I adjust the function to meet the expectation?

Upvotes: 0

Views: 2583

Answers (1)

Zhorov
Zhorov

Reputation: 29943

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

Upvotes: 1

Related Questions