ספט29
Written by:
ronen ariely
29/09/2019 07:36 
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.
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
