I came up with this article when in my job, I had a request to (fetch and) and parse JSON data inside of a SQL Server procedure.
当我在工作中遇到一个请求（获取并）并解析SQL Server过程中的JSON数据时，我想到了这篇文章。
So, I can provide here the function that is the outcome of that work, for your free usage. It should be noted that it will just do the "raw" parsing, but not "decode" the JSON string values, and does also not determine if the numbers defined are really in a correct notation.
因此，我可以在此处提供该工作的成果，供您免费使用。 应该注意的是，它只会进行“原始”解析，而不会“解码” JSON字符串值，并且也不会确定所定义的数字是否真的采用正确的表示法。
The rules I considered to implement in this function are from this page: http://www.json.org/
我认为要在此功能中实现的规则来自以下页面： http : //www.json.org/
create function dbo.jsontotable(@json varchar(max))
returns @table table (id int identity, name varchar(max), value varchar(max), level int , object_type varchar(100) )
as
begin
-- http://www.json.org/
-- we want to partse out objects:
-- object : { string : value <, repeat> }
-- string : " <anything except \ and "> or <\ followed by "\/bfnrt> or < \unnnn > repeat"
-- array : [ value <, repeat>]
-- value : object (starting with { ) or string starting with " ) or array (starting with [ ) or true or false or null or number
-- number optionally - digit(s) optionally . followed by digits and/or optionall e/E followed by digits
-- so, we part character by character, waiting for the next special start/end to happen
declare @pos int
declare @len int
declare @c char(1)
declare @level int = 0
declare @status int = 0
-- 0: should start object : {
-- 1: should start name : "
-- 2: waiting for end of string (") of name
-- 3: should get :
-- 4: check value start : could be " for value or [ for array or { for object or true or false or null or number could be ] as array end
-- 5: waiting for end of string (") of value (special handling of \)
-- 9: waiting for end of number
--10: checking next part, could be , to continue, or ] end of array or } end object
--11: checking next array part, could be status 0 or 1
declare @name varchar(max)
declare @value varchar(max)
declare @name_start int
declare @value_start int
declare @object_type varchar(100)
declare @array_levels varchar(100) = ','
set @pos = 1
set @len = len(@json)
while @pos <= @len
begin
set @c = substring(@json, @pos, 1)
if @c = ' '
or @c = char(9) -- tab
or @c = char(10) -- nl
or @c = char(13) -- cr
begin
-- skip this character to ignore whitespace between tokens
goto next_loop
end
-- waiting for object start
if @status in (0 ,11)
begin
if @c = '{'
begin
insert into @table ( name , object_type, value, level) values (null, 'object' , null, @level)
set @status = 1 -- wait for object name, starting with "
set @level = @level + 1
end
else
begin
if @status = 0
begin
insert into @table ( object_type ) values ( 'expected object start' )
return
end
end
goto next_loop
end -- if @status = 0
--waiting for pair name
if @status in ( 1,11)
begin
if @c = '"'
begin
set @name_start = @pos+1
set @object_type = 'value'
set @status = 2
end
else
begin
if @status = 1
begin
insert into @table ( object_type, value , level) values ( 'expected string start' , @c, @level)
return
end
end
goto next_loop
end
--waiting for string end
if @status in ( 2,5 )
begin
if @c = '\'
begin
--skip 1 position to bypass any " at that place
set @pos = @pos + 1
end
else
begin
if @c = '"'
begin
if @status = 2
begin
-- get the unescaped name (may contain \ ...)
set @name = substring(@json, @name_start, @pos - @name_start)
set @status = 3
end
else
begin
-- get the unescaped value (may contain \ ...)
set @value = substring(@json, @value_start, @pos - @value_start)
insert into @table ( object_type, value, name, level ) values ( @object_type, @value , @name, @level)
-- after the value, check what comes next
set @status = 4
end
end
end
goto next_loop
end -- if @status in ( 2,5 )
--checking for : to delimit the name:value pair
if @status = 3
begin
if @c = ':'
begin
set @status = 4
end
else
begin
insert into @table ( object_type, value , level) values ( 'expected column' , @c, @level)
return
end
goto next_loop
end -- if @status in ( 3 )
if @status in ( 4 )
begin
if @c = '"'
begin
--if @array_levels like '%,' +cast(@level as varchar(10)) + ',%'
begin
set @value_start = @pos + 1
set @status = 5
end
end
if @c = ']'
begin
--go down 1 level
set @level = @level - 1
--we continue to wait for an object or next step
set @status = 4
end
-- 4: check value start : could be " for value or [ for array or { for object or true or false or null or number could be ] as array end
if @c = '['
begin
set @object_type = 'array'
insert into @table ( name , object_type, level) values (@name, @object_type , @level)
--go up 1 level
set @object_type = 'item'
set @name = null
set @level = @level + 1
--we continue to wait for an object
set @status = 4
end
if @c = '{'
begin
set @object_type = 'object'
--we want now to start the object name
set @status = 0
set @pos = @pos - 1
end
if @c = '}'
begin
--go down 1 level
set @level = @level - 1
--what's next
set @status = 4
end
if @c = 'n'
begin -- starting null?
if substring(@json, @pos, 4) = 'null'
begin
insert into @table ( name , object_type, level) values (@name, 'null', @level)
set @pos = @pos +3
set @status = 4
end
else
begin
insert into @table ( value , object_type, level) values (substring(@json, @pos, 4), 'expected null', @level)
end
--what comes next?
set @status = 4
end
if @c = 't'
begin -- starting null?
if substring(@json, @pos, 4) = 'true'
begin
insert into @table ( name , object_type, value, level) values (@name, 'boolean' , 'true', @level)
set @pos = @pos +3
set @status = 4
end
else
begin
insert into @table ( value , object_type, level) values (substring(@json, @pos, 4), 'expected true', @level)
end
--what comes next?
set @status = 4
end
if @c = 'f'
begin -- starting null?
if substring(@json, @pos, 5) = 'false'
begin
insert into @table ( name , object_type, value, level) values (@name, 'boolean' , 'false', @level)
set @pos = @pos +4
set @status = 4
end
else
begin
insert into @table ( value , object_type, level) values (substring(@json, @pos, 5), 'expected false', @level)
end
--what comes next?
set @status = 4
end
if @c = ','
begin
set @name = null
--maybe object or pair
-- are we inside an object or an array;
select top 1 @status = case when object_type = 'object' then 1 else 4 end
from @table where level = @level - 1 order by id desc
end
if @c like '[0-9-]'
begin
set @status = 9
set @value_start = @pos
set @object_type ='number'
end
goto next_loop
end-- if @status in ( 4 )
if @status = 9
begin
if @c like '[eE.0-9+-]'
begin
-- continue waiting
set @status = 9
end
else
begin
set @value = substring(@json, @value_start, @pos - @value_start)
insert into @table ( object_type, value, name, level ) values ( @object_type, @value , @name, @level)
--get next part
set @status = 4
set @pos = @pos -1
end
goto next_loop
end
--insert into @table ( name ) values (@c)
next_loop:
set @pos = @pos + 1
end -- loop
return
end
declare @json varchar(max)
set @json = ' { "id"
: "9 \\a","ob":[{"a":true}
,{"a":"C","c":-1.4e+4}]
,"x":["a","B"]
}'
select * from dbo.jsontotable(@json )
- id: identity column, order of the parsing id：标识列，解析顺序
- name: name of value pair 名称：值对的名称
- value: value of value pair 值：值对的值
- level: the hierarchy level 级别：层次结构级别
- object type: (object, array, value, boolean, number, null ...) 对象类型：（对象，数组，值，布尔值，数字，空值...）
翻译自: https://www.experts-exchange.com/articles/28500/Parse-JSON-into-a-hierarchical-table.html
扫一扫
1211
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
