en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP 


 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize
ספט29

Written by: ronen ariely
29/09/2019 07:36 RssIcon

OPENJSON is a build-in SQL Server table-valued function (return tabular structure), which parse text that formatted as JSON. By default, the function only parse the first level of the JSON document, but using explicit path to the nodes in the document we can parse the entire document including unlimited level of hierarchy. This sound great except the fact that JSON document is commonly used for unstructured data, meaning we do not know what nodes the document will have and what is the the path to each node... Unfortunately, OPENJSON does not provide a dynamic parsing for hierarchy structure.

In this blog, I will present a simple recursive table-valued function, which based on OPENJSON and provide full dynamic hierarchy parsing of JSON document.

Note! This post follows this question in the MSDN forums.

Solution 1: returns only the values (types 1-3)

The following query is the fastest and simplest option to parse the value (in this sample a variable, but it can be executed on table as well)

with MyCTE as(
    select
        [key],[value],
        [Path]=cast(concat('$."',[key],'"') as nvarchar(max))
        ,[type]
    from openjson(@JSON_Text)
    union all
    select
        k.[key],k.[value],
        [Path]=cast((case
            when c.[type]=4 then concat(c.[Path],'[',k.[key],']')
            else concat(c.[Path],'."',k.[key],'"')
        end) as nvarchar(max)),k.[type]
    from MyCTE c
        outer apply openjson(c.[value]) k
    where c.[type]>3
)
select *
from MyCTE
where [type]<4;
go

If we have table then we will need to use the uniqe column in order to get parse the values of each column seperatelly.

DROP TABLE IF EXISTS JT
GO
CREATE TABLE JT (id int identity(2,2), j nvarchar(MAX))
GO
 
-- Insert some rows with JSON
 
 
;with MyCTE as(
    select JT.id,
        [key],[value],
        [Path]=cast(concat('$."',[key],'"') as nvarchar(max))
        ,[type]
    from JT
    outer apply openjson(JT.j)
    union all
    select c.id,
        k.[key],k.[value],
        [Path]=cast((case
            when c.[type]=4 then concat(c.[Path],'[',k.[key],']')
            else concat(c.[Path],'."',k.[key],'"')
        end) as nvarchar(max)),k.[type]
    from MyCTE c
        outer apply openjson(c.[value]) k
    where c.[type]>3
)
select *
from MyCTE
where [type]<4;
go


Solution 2: returns all entities including the Object and Arrays

The following query parse the JSON and into HierarchyID data tye

DROP FUNCTION IF EXISTS RonenAriely_HierarchyOpenjson;
GO
create or alter function RonenAriely_HierarchyOpenjson(
      @value NVARCHAR(max)
    , @PreType int
    , @PrePath NVARCHAR(MAX)
    , @hierarchyid hierarchyid
)
returns @tempTable table (
      [key] NVARCHAR(max)
    , [value] NVARCHAR(max)
    , [PreType] int
    , [type] int
    , [PrePath] NVARCHAR(MAX)
    , [path] NVARCHAR(MAX)
    , [hierarchyid] HIERARCHYID
)
as begin
    ;with MyCTE as (
        select
            [key],
            [value],
            [PreType] = @PreType,
            [type],
            [PrePath] = @PrePath,
            [path] = iif(
                @PrePath is null,
                [key],
                iif(@PreType = 4, CONCAT(@PrePath, N'[', [key],N']'), CONCAT(@PrePath, N'.', [key]))
            ) ,
            [hierarchyid] = iif(
                @hierarchyid is null,
                --hierarchyid::GetRoot(),
                CONVERT(hierarchyid,CONCAT(N'/',ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),N'/')),
                CONVERT(hierarchyid,
                    CONCAT(
                        @hierarchyid.ToString(),
                        ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
                        ,N'/'
                    )
                )
            )
        from openjson(@value)
    )
    insert @tempTable
          
        -- type 1,2,3,4,5
        select
              c.[key]
            , c.[value]
            , c.[PreType]
            , c.[type]
            , c.[PrePath]
            , c.[path]
            , c.[hierarchyid]
        from MyCTE c
  
        --------------------------- Recursive for types 4 and 5
        -- types 5
        union all
        select
              ca.[key]
            , ca.[value]
            , ca.[PreType]
            , ca.[type]
            , ca.[PrePath]
            , ca.[path]
            , ca.[hierarchyid]
        from MyCTE c
        cross apply RonenAriely_HierarchyOpenjson(
              c.[value]
            , c.[type]
            , c.[path]
            , c.[hierarchyid]
        ) ca
        where  c.[type]>3
            -- I add check that each value fit as JSON before parse it
            -- This reduce performance but improve consistency
            -- If you sure 100% that all text is well formatted as JSON
            --   then you can remove this filter
            and isjson(c.[value])=1
          
    return
end

Demo 01:

SELECT * , [hierarchyid].ToString()
FROM dbo.RonenAriely_HierarchyOpenjson('{    "Person":
    {
       "firstName": "John",
       "lastName": "Smith",
       "age": 25,
       "Address":
       {
          "streetAddress":"21 2nd Street",
          "city":"New York",
          "state":"NY",
          "postalCode":"10021"
       },
       "PhoneNumbers":
       {
          "home":"212 555-1234",
          "fax":"646 555-4567"
       }
    }
  }',null,null, null
)
order by [hierarchyid]

Demo 02:


-- the complex is to build the PATH
-- the more issue is to work with arrays
 
DECLARE @JSON_Text NVARCHAR(MAX) = '{
"$id""somesourceID",
"additionalProperties": false,
"properties": {
"Data": {
"properties": {
"ExtensionProperties": {
"properties": {},
"type""object"
},
"GroupTypeProperties": {
"properties": {},
"type""object"
},
"IndividualTypeProperties": {
"allOf": [{
"$ref""/reference-data/AbstractFacility/"
},
{
"properties": {
"BlockID": {
"description""The block where the well is located offshore.",
"pattern""srn:master-data/GeopoliticalEntity:[^:/]+:[0-9]*$",
"type""string"
},
"CountryID": {
"description""The geopolitical country where the well is located.",
"pattern""srn:master-data/GeopoliticalEntity:[^:/]+:[0-9]*$",
"type""string"
},
"CountyID": {
"description""The county where the well is located.",
"pattern""srn:master-data/GeopoliticalEntity:[^:/]+:[0-9]*$",
"type""string"
},
"GroundLevelElevation": {
"$ref""/reference-data/GroundLevelElevation/",
"description""The default vertical coordinate reference system used in the vertical starting reference point for a wells vertical measurements and elevation distance with UoM (at the time of spud). Vertical CRS like mean sea level."
},
"InterestTypeID": {
"description""Pre-defined reasons for why  has gotten or interested in the well.",
"pattern""srn:reference-data/WellInterestType:[^:/]+:[0-9]*$",
"type""string"
},
"QuadrantID": {
"description""The quadrant where the well is located offshore.",
"pattern""srn:master-data/GeopoliticalEntity:[^:/]+:[0-9]*$",
"type""string"
},
"StateProvinceID": {
"description""The state/province where the well is located.",
"pattern""srn:master-data/GeopoliticalEntity:[^:/]+:[0-9]*$",
"type""string"
},
"ZeroDepthPoint": {
"description""The default datum reference point used to determine other points in a WELL and The default vertical coordinate reference system used in the vertical starting reference point for a wells vertical measurements and elevation distance with UoM (at the time of spud). Vertical CRS like mean sea level.",
"items": {
"$ref""/reference-data/AbstractZeroDepthPoint/"
},
"type""array"
}
},
"type""object"
}
]
}
},
"type""object"
},
"ResourceCurationStatus": {
"description""Describes the current Curation status. Possible values - CREATED, CURATING, CURATED.",
"pattern""srn:reference-data/ResourceCurationStatus:[^:/]+:[0-9]*$",
"type""string"
},
"ResourceHomeRegionID": {
"description""The name of the home [cloud environment] region for this  resource object.",
"pattern""srn:reference-data/Region:[^:/]+:[0-9]*$",
"type""string"
},
"ResourceHostRegionIDs": {
"description""The name of the host [cloud environment] region(s) for this  resource object.",
"items": {
"pattern""srn:reference-data/Region:[^:/]+:[0-9]*$",
"type""string"
},
"type""array"
},
"ResourceID": {
"description""The SRN which identifies this  resource object at the version level.",
"pattern""srn:master-data/Well:[^:/]+:[0-9]+$",
"type""string"
},
"ResourceLifecycleStatus": {
"description""Describes the current Resource Lifecycle status. Possible values - LOADING, RECIEVED, ACCEPTED, RESCINDED, DELETED,",
"pattern""srn:reference-data/ResourceLifecycleStatus:[^:/]+:[0-9]*$",
"type""string"
},
"ResourceObjectCreationDateTime": {
"description""Timestamp of the time at which Version 1 of this  resource object was originated.",
"format""date-time",
"type""string"
},
"ResourceSecurityClassification": {
"description""Classifies the security level of the resourcse. Possible values = RESTRICTED, CLASSIFIED, CONFIDENTIAL, MOST CONFIDENTIAL  ",
"pattern""srn:reference-data/ResourceSecurityClassification:[^:/]+:[0-9]*$",
"type""string"
},
"ResourceTypeID": {
"description""The SRN of the resources resource type.",
"pattern""srn:type:master-data/Well:[0-9]*$",
"type""string"
},
"ResourceVersionCreationDateTime": {
"description""Timestamp of the time when the current version of this resource entered the .",
"format""date-time",
"type""string"
}
},
"required": [
"ResourceTypeID",
"ResourceID",
"ResourceHomeRegionID",
"ResourceHostRegionIDs",
"ResourceObjectCreationDateTime",
"ResourceVersionCreationDateTime",
"ResourceCurationStatus",
"ResourceLifecycleStatus",
"ResourceSecurityClassification"
],
"title""Well",
"type""object"
}'
 
SELECT *, [hierarchyid].ToString()
FROM dbo.RonenAriely_HierarchyOpenjson(@JSON_Text,null,null, null)
order by [hierarchyid]
GO