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 : https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-2017 ; https://learn.microsoft.com/en-us/sql/relational-databases/json/convert-json-data-to-rows-and-columns-with-openjson-sql-server?view=sql-server-2017; https://stackoverflow.com/questions/2867501/parse-json-in-tsql — Sanpas, Mar 04 '19 at 11:09
Possible duplicate of [Parse JSON in TSQL](https://stackoverflow.com/questions/2867501/parse-json-in-tsql) — Sanpas, Mar 04 '19 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, Mar 04 '19 at 11:18

Zhorov · Accepted Answer · 2019-03-06T06:51:13.717

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

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.` — Annie, Mar 06 '19 at 06:32
@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. — Zhorov, Mar 06 '19 at 06:51

How to parse JSON array string by using mssql?

1 Answers1