在 TSQL 中解析 JSON 是否可能？
我的意思不是要创建一个 JSON 字符串，而是要解析作为 参数 传入的 JSON 字符串。
在T-SQL中解析JSON
128
- R0b0tn1k
10个回答
248
我好像有一个巨大的受虐倾向，因为我写了一个JSON解析器。它可以将JSON文档转换为SQL邻接表，这样就可以轻松更新数据表。实际上，我做得更糟糕，我还编写了代码来执行反向过程，即从层次结构表到JSON字符串的转换。
这里是文章和代码链接：Consuming Json strings in SQL server。
这里是文章和代码链接：Consuming Json strings in SQL server。
Select * from parseJSON('{
"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"
}
}
}
')
要获取：
- Phil Factor
10
65
更新：截至SQL Server 2016，在TSQL中解析JSON现在是可能的。
原生地，不支持此功能。您必须使用CLR。除非您有一个巨大的受虐倾向并想在SQL中编写JSON解析器，否则就像这样简单。
通常，人们要求从数据库输出JSON，并且互联网上有示例。但是将其插入到数据库中呢？
- gbn
5
63JSON是一种相当简单的协议，因此它并不需要大量的折磨。一旦你有了它，你可以使用一个例程来处理所有的JSON。无论如何，我在这里为你完成了这个任务：http://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/ - Phil Factor
11Phil Factor：我已经读了你的文章很多年了。如果你不是在今天写这篇文章的话，我可能会在6个月前引用它来回答... - gbn
10SQL Server 2016内置了对解析JSON文本的支持。 - Jovan MSFT
1这是一篇非常有用的文章，来自Simple Talk网站，概述了如何将JSon字符串输出到可以查询的表格和列中。这适用于SQL Server 2016：https://www.simple-talk.com/sql/learn-sql-server/json-support-in-sql-server-2016/ - codeaf
1以下是一个使用CLR方法解析JSON的代码示例：https://blog.dotnetframework.org/2016/12/06/parse-json-in-ms-sql-server-procurios/ - Fiach Reid
28
最终SQL Server 2016将增加原生JSON支持！！
参考：
SQL Server 2016的其他功能包括：
- 为行级安全性和动态数据脱敏增加了额外的安全增强功能，以完善我们的Always Encrypted安全投资。
- 通过多个同步副本和辅助负载平衡，改进了AlwaysOn以实现更强大的可用性和灾难恢复。
- 原生JSON支持提供更好的性能和对多种类型数据的支持。
- SQL Server企业信息管理（EIM）工具和分析服务在性能、可用性和可扩展性方面得到升级。
- 更快的混合备份、高可用性和灾难恢复场景，将您的本地数据库备份和还原到Azure，并将您的SQL Server AlwaysOn次要副本放置在Azure中。
特性博客文章：http://blogs.msdn.com/b/jocapc/archive/2015/05/16/json-support-in-sql-server-2016.aspx
- Irvin Dominin
1
2很可能SQL Server 2016 CTP 3将支持JSON到SQL Server的转换，并使用OpenJSON语法：http://www.kodyaz.com/t-sql/sql-server-2016-openjson-error.aspx - Eralper
8
SQL Server 2016支持使用OPENJSON解析JSON数据。您可以使用OPENJSON将JSON数据映射到行和列。
您的JSON数据
[
{ "id" : 2,"name": "John"},
{ "id" : 5,"name": "John"}
]
以下是如何在SQL中处理JSON的方法：
//@pJson is json data passed from code.
INSERT INTO YourTable (id, Name)
SELECT id, name
FROM OPENJSON(@pJson)
WITH (id int,
name nvarchar(max))
这里有一篇详细的文章介绍了如何将JSON文本插入SQL Server表中。
- Mairaj Ahmad
7
我之前开发了自己的SQL Server 2016+ JSON解析器，并在我的所有项目中使用它，性能非常出色。我希望这可以帮助其他人。
函数的完整代码：
ALTER FUNCTION [dbo].[SmartParseJSON] (@json NVARCHAR(MAX))
RETURNS @Parsed TABLE (Parent NVARCHAR(MAX),Path NVARCHAR(MAX),Level INT,Param NVARCHAR(4000),Type NVARCHAR(255),Value NVARCHAR(MAX),GenericPath NVARCHAR(MAX))
AS
BEGIN
-- Author: Vitaly Borisov
-- Create date: 2018-03-23
;WITH crData AS (
SELECT CAST(NULL AS NVARCHAR(4000)) COLLATE DATABASE_DEFAULT AS [Parent]
,j.[Key] AS [Param],j.Value,j.Type
,j.[Key] AS [Path],0 AS [Level]
,j.[Key] AS [GenericPath]
FROM OPENJSON(@json) j
UNION ALL
SELECT CAST(d.Path AS NVARCHAR(4000)) COLLATE DATABASE_DEFAULT AS [Parent]
,j.[Key] AS [Param],j.Value,j.Type
,d.Path + CASE d.Type WHEN 5 THEN '.' WHEN 4 THEN '[' ELSE '' END + j.[Key] + CASE d.Type WHEN 4 THEN ']' ELSE '' END AS [Path]
,d.Level+1
,d.GenericPath + CASE d.Type WHEN 5 THEN '.' + j.[Key] ELSE '' END AS [GenericPath]
FROM crData d
CROSS APPLY OPENJSON(d.Value) j
WHERE ISJSON(d.Value) = 1
)
INSERT INTO @Parsed(Parent, Path, Level, Param, Type, Value, GenericPath)
SELECT d.Parent,d.Path,d.Level,d.Param
,CASE d.Type
WHEN 1 THEN CASE WHEN TRY_CONVERT(UNIQUEIDENTIFIER,d.Value) IS NOT NULL THEN 'UNIQUEIDENTIFIER' ELSE 'NVARCHAR(MAX)' END
WHEN 2 THEN 'INT'
WHEN 3 THEN 'BIT'
WHEN 4 THEN 'Array'
WHEN 5 THEN 'Object'
ELSE 'NVARCHAR(MAX)'
END AS [Type]
,CASE
WHEN d.Type = 3 AND d.Value = 'true' THEN '1'
WHEN d.Type = 3 AND d.Value = 'false' THEN '0'
ELSE d.Value
END AS [Value]
,d.GenericPath
FROM crData d
OPTION(MAXRECURSION 1000) /*Limit to 1000 levels deep*/
;
RETURN;
END
GO
使用示例：
DECLARE @json NVARCHAR(MAX) = '{"Objects":[{"SomeKeyID":1,"Value":3}],"SomeParam":"Lalala"}';
SELECT j.Parent, j.Path, j.Level, j.Param, j.Type, j.Value, j.GenericPath
FROM dbo.SmartParseJSON(@json) j;
多级使用示例：
DECLARE @json NVARCHAR(MAX) = '{"Objects":[{"SomeKeyID":1,"Value":3}],"SomeParam":"Lalala"}';
DROP TABLE IF EXISTS #ParsedData;
SELECT j.Parent, j.Path, j.Level, j.Param, j.Type, j.Value, j.GenericPath
INTO #ParsedData
FROM dbo.SmartParseJSON(@json) j;
SELECT COALESCE(p2.GenericPath,p.GenericPath) AS [GenericPath]
,COALESCE(p2.Param,p.Param) AS [Param]
,COALESCE(p2.Value,p.Value) AS [Value]
FROM #ParsedData p
LEFT JOIN #ParsedData p1 ON p1.Parent = p.Path AND p1.Level = 1
LEFT JOIN #ParsedData p2 ON p2.Parent = p1.Path AND p2.Level = 2
WHERE p.Level = 0
;
DROP TABLE IF EXISTS #ParsedData;
- Vitaly Borisov
1
1感谢分享。 - André Voltolini
5
我确实有一种 masochistic streak（虐待狂倾向），因此我又写了一个 JSON 解析器。这个解析器采用过程化方法，并使用类似 SQL 层次列表表格来存储已解析的数据。包中还包括以下功能：
- 反向处理：从层次结构转换回 JSON
- 查询函数：从 JSON 对象中获取特定值
- jsegarra
1
+1 谢谢，非常好用，比 PhilFactor 版本更好。不过我必须稍微简化一下，以适应 SQL Server 2008（没有
iif 函数或 OFFSET）。 - egerardus2
现在SQL Server（CTP3）具备原生支持，可以在T-SQL中导入、导出、查询和验证JSON。请参阅https://msdn.microsoft.com/en-us/library/dn921897.aspx
- Mo Zaatar
0
CREATE FUNCTION dbo.parseJSON( @JSON NVARCHAR(MAX))
RETURNS @hierarchy TABLE
(
element_id INT IDENTITY(1, 1) NOT NULL, /* internal surrogate primary key gives the order of parsing and the list order */
sequenceNo [int] NULL, /* the place in the sequence for the element */
parent_ID INT,/* if the element has a parent then it is in this column. The document is the ultimate parent, so you can get the structure from recursing from the document */
Object_ID INT,/* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */
NAME NVARCHAR(2000),/* the name of the object */
StringValue NVARCHAR(MAX) NOT NULL,/*the string representation of the value of the element. */
ValueType VARCHAR(10) NOT null /* the declared type of the value represented as a string in StringValue*/
)
AS
BEGIN
DECLARE
@FirstObject INT, --the index of the first open bracket found in the JSON string
@OpenDelimiter INT,--the index of the next open bracket found in the JSON string
@NextOpenDelimiter INT,--the index of subsequent open bracket found in the JSON string
@NextCloseDelimiter INT,--the index of subsequent close bracket found in the JSON string
@Type NVARCHAR(10),--whether it denotes an object or an array
@NextCloseDelimiterChar CHAR(1),--either a '}' or a ']'
@Contents NVARCHAR(MAX), --the unparsed contents of the bracketed expression
@Start INT, --index of the start of the token that you are parsing
@end INT,--index of the end of the token that you are parsing
@param INT,--the parameter at the end of the next Object/Array token
@EndOfName INT,--the index of the start of the parameter at end of Object/Array token
@token NVARCHAR(200),--either a string or object
@value NVARCHAR(MAX), -- the value as a string
@SequenceNo int, -- the sequence number within a list
@name NVARCHAR(200), --the name as a string
@parent_ID INT,--the next parent ID to allocate
@lenJSON INT,--the current length of the JSON String
@characters NCHAR(36),--used to convert hex to decimal
@result BIGINT,--the value of the hex symbol being parsed
@index SMALLINT,--used for parsing the hex value
@Escape INT --the index of the next escape character
DECLARE @Strings TABLE /* in this temporary table we keep all strings, even the names of the elements, since they are 'escaped' in a different way, and may contain, unescaped, brackets denoting objects or lists. These are replaced in the JSON string by tokens representing the string */
(
String_ID INT IDENTITY(1, 1),
StringValue NVARCHAR(MAX)
)
SELECT--initialise the characters to convert hex to ascii
@characters='0123456789abcdefghijklmnopqrstuvwxyz',
@SequenceNo=0, --set the sequence no. to something sensible.
/* firstly we process all strings. This is done because [{} and ] aren't escaped in strings, which complicates an iterative parse. */
@parent_ID=0;
WHILE 1=1 --forever until there is nothing more to do
BEGIN
SELECT
@start=PATINDEX('%[^a-zA-Z]["]%', @json collate SQL_Latin1_General_CP850_Bin);--next delimited string
IF @start=0 BREAK --no more so drop through the WHILE loop
IF SUBSTRING(@json, @start+1, 1)='"'
BEGIN --Delimited Name
SET @start=@Start+1;
SET @end=PATINDEX('%[^\]["]%', RIGHT(@json, LEN(@json+'|')-@start) collate SQL_Latin1_General_CP850_Bin);
END
IF @end=0 --no end delimiter to last string
BREAK --no more
SELECT @token=SUBSTRING(@json, @start+1, @end-1)
--now put in the escaped control characters
SELECT @token=REPLACE(@token, FROMString, TOString)
FROM
(SELECT
'\"' AS FromString, '"' AS ToString
UNION ALL SELECT '\\', '\'
UNION ALL SELECT '\/', '/'
UNION ALL SELECT '\b', CHAR(08)
UNION ALL SELECT '\f', CHAR(12)
UNION ALL SELECT '\n', CHAR(10)
UNION ALL SELECT '\r', CHAR(13)
UNION ALL SELECT '\t', CHAR(09)
) substitutions
SELECT @result=0, @escape=1
--Begin to take out any hex escape codes
WHILE @escape>0
BEGIN
SELECT @index=0,
--find the next hex escape sequence
@escape=PATINDEX('%\x[0-9a-f][0-9a-f][0-9a-f][0-9a-f]%', @token collate SQL_Latin1_General_CP850_Bin)
IF @escape>0 --if there is one
BEGIN
WHILE @index<4 --there are always four digits to a \x sequence
BEGIN
SELECT --determine its value
@result=@result+POWER(16, @index)
*(CHARINDEX(SUBSTRING(@token, @escape+2+3-@index, 1),
@characters)-1), @index=@index+1 ;
END
-- and replace the hex sequence by its unicode value
SELECT @token=STUFF(@token, @escape, 6, NCHAR(@result))
END
END
--now store the string away
INSERT INTO @Strings (StringValue) SELECT @token
-- and replace the string with a token
SELECT @JSON=STUFF(@json, @start, @end+1,
'@string'+CONVERT(NVARCHAR(5), @@identity))
END
-- all strings are now removed. Now we find the first leaf.
WHILE 1=1 --forever until there is nothing more to do
BEGIN
SELECT @parent_ID=@parent_ID+1
--find the first object or list by looking for the open bracket
SELECT @FirstObject=PATINDEX('%[{[[]%', @json collate SQL_Latin1_General_CP850_Bin)--object or array
IF @FirstObject = 0 BREAK
IF (SUBSTRING(@json, @FirstObject, 1)='{')
SELECT @NextCloseDelimiterChar='}', @type='object'
ELSE
SELECT @NextCloseDelimiterChar=']', @type='array'
SELECT @OpenDelimiter=@firstObject
WHILE 1=1 --find the innermost object or list...
BEGIN
SELECT
@lenJSON=LEN(@JSON+'|')-1
--find the matching close-delimiter proceeding after the open-delimiter
SELECT
@NextCloseDelimiter=CHARINDEX(@NextCloseDelimiterChar, @json,
@OpenDelimiter+1)
--is there an intervening open-delimiter of either type
SELECT @NextOpenDelimiter=PATINDEX('%[{[[]%',
RIGHT(@json, @lenJSON-@OpenDelimiter)collate SQL_Latin1_General_CP850_Bin)--object
IF @NextOpenDelimiter=0
BREAK
SELECT @NextOpenDelimiter=@NextOpenDelimiter+@OpenDelimiter
IF @NextCloseDelimiter<@NextOpenDelimiter
BREAK
IF SUBSTRING(@json, @NextOpenDelimiter, 1)='{'
SELECT @NextCloseDelimiterChar='}', @type='object'
ELSE
SELECT @NextCloseDelimiterChar=']', @type='array'
SELECT @OpenDelimiter=@NextOpenDelimiter
END
---and parse out the list or name/value pairs
SELECT
@contents=SUBSTRING(@json, @OpenDelimiter+1,
@NextCloseDelimiter-@OpenDelimiter-1)
SELECT
@JSON=STUFF(@json, @OpenDelimiter,
@NextCloseDelimiter-@OpenDelimiter+1,
'@'+@type+CONVERT(NVARCHAR(5), @parent_ID))
WHILE (PATINDEX('%[A-Za-z0-9@+.e]%', @contents collate SQL_Latin1_General_CP850_Bin))<>0
BEGIN
IF @Type='Object' --it will be a 0-n list containing a string followed by a string, number,boolean, or null
BEGIN
SELECT
@SequenceNo=0,@end=CHARINDEX(':', ' '+@contents)--if there is anything, it will be a string-based name.
SELECT @start=PATINDEX('%[^A-Za-z@][@]%', ' '+@contents collate SQL_Latin1_General_CP850_Bin)--AAAAAAAA
SELECT @token=SUBSTRING(' '+@contents, @start+1, @End-@Start-1),
@endofname=PATINDEX('%[0-9]%', @token collate SQL_Latin1_General_CP850_Bin),
@param=RIGHT(@token, LEN(@token)-@endofname+1)
SELECT
@token=LEFT(@token, @endofname-1),
@Contents=RIGHT(' '+@contents, LEN(' '+@contents+'|')-@end-1)
SELECT @name=stringvalue FROM @strings
WHERE string_id=@param --fetch the name
END
ELSE
SELECT @Name=null,@SequenceNo=@SequenceNo+1
SELECT
@end=CHARINDEX(',', @contents)-- a string-token, object-token, list-token, number,boolean, or null
IF @end=0
SELECT @end=PATINDEX('%[A-Za-z0-9@+.e][^A-Za-z0-9@+.e]%', @Contents+' ' collate SQL_Latin1_General_CP850_Bin)
+1
SELECT
@start=PATINDEX('%[^A-Za-z0-9@+.e][A-Za-z0-9@+.e]%', ' '+@contents collate SQL_Latin1_General_CP850_Bin)
--select @start,@end, LEN(@contents+'|'), @contents
SELECT
@Value=RTRIM(SUBSTRING(@contents, @start, @End-@Start)),
@Contents=RIGHT(@contents+' ', LEN(@contents+'|')-@end)
IF SUBSTRING(@value, 1, 7)='@object'
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType)
SELECT @name, @SequenceNo, @parent_ID, SUBSTRING(@value, 8, 5),
SUBSTRING(@value, 8, 5), 'object'
ELSE
IF SUBSTRING(@value, 1, 6)='@array'
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType)
SELECT @name, @SequenceNo, @parent_ID, SUBSTRING(@value, 7, 5),
SUBSTRING(@value, 7, 5), 'array'
ELSE
IF SUBSTRING(@value, 1, 7)='@string'
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, ValueType)
SELECT @name, @SequenceNo, @parent_ID, stringvalue, 'string'
FROM @strings
WHERE string_id=SUBSTRING(@value, 8, 5)
ELSE
IF @value IN ('true', 'false')
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, ValueType)
SELECT @name, @SequenceNo, @parent_ID, @value, 'boolean'
ELSE
IF @value='null'
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, ValueType)
SELECT @name, @SequenceNo, @parent_ID, @value, 'null'
ELSE
IF PATINDEX('%[^0-9]%', @value collate SQL_Latin1_General_CP850_Bin)>0
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, ValueType)
SELECT @name, @SequenceNo, @parent_ID, @value, 'real'
ELSE
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, ValueType)
SELECT @name, @SequenceNo, @parent_ID, @value, 'int'
if @Contents=' ' Select @SequenceNo=0
END
END
INSERT INTO @hierarchy (NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType)
SELECT '-',1, NULL, '', @parent_id-1, @type
--
RETURN
END
GO
---解析JSON
Declare @pars varchar(MAX) =
' {"shapes":[{"type":"polygon","geofenceName":"","geofenceDescription":"",
"geofenceCategory":"1","color":"#1E90FF","paths":[{"path":[{
"lat":"26.096254906968525","lon":"65.709228515625"}
,{"lat":"28.38173504322308","lon":"66.741943359375"}
,{"lat":"26.765230565697482","lon":"68.983154296875"}
,{"lat":"26.254009699865737","lon":"68.609619140625"}
,{"lat":"25.997549919572112","lon":"68.104248046875"}
,{"lat":"26.843677401113002","lon":"67.115478515625"}
,{"lat":"25.363882272740255","lon":"65.819091796875"}]}]}]}'
Select * from parseJSON(@pars) AS MyResult
- Addi Khan
2
3对于没有任何解释或链接的复制粘贴他人答案的行为是不好的。此解决方案来自RedGate，撰写于2010年11月。https://www.red-gate.com/simple-talk/sql/t-sql-programming/consuming-json-strings-in-sql-server/
对于运行SQL Server 2016+的用户，请不要使用此方法，因为SQL Server已引入本地解决方案。 - MGot90
https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server - MGot90
0
从SQL Server 2016及更高版本开始，它已经得到本地支持。
SELECT Name, Surname,
JSON_VALUE(jsonCol, '$.info.address.PostCode') AS PostCode,
JSON_VALUE(jsonCol, '$.info.address."Address Line 1"') + ' '
+ JSON_VALUE(jsonCol, '$.info.address."Address Line 2"') AS Address,
JSON_QUERY(jsonCol, '$.info.skills') AS Skills
FROM People
WHERE ISJSON(jsonCol) > 0
AND JSON_VALUE(jsonCol, '$.info.address.Town') = 'Belgrade'
AND Status = 'Active'
ORDER BY JSON_VALUE(jsonCol, '$.info.address.PostCode')
您甚至可以使用JSON_MODIFY()修改JSON字符串中的单个值：
DECLARE @json NVARCHAR(MAX);
SET @json = '{"info": {"address": [{"town": "Belgrade"}, {"town": "Paris"}, {"town":"Madrid"}]}}';
SET @json = JSON_MODIFY(@json, '$.info.address[1].town', 'London');
SELECT modifiedJson = @json;
这将给你 {"info":{"address":[{"town":"贝尔格莱德"},{"town":"伦敦"},{"town":"马德里"}]}
- josibu
-4
我看过一篇非常棒的关于这个的文章...所以如果你喜欢这个：
CREATE PROC [dbo].[spUpdateMarks]
@inputJSON VARCHAR(MAX) -- '[{"ID":"1","C":"60","CPP":"60","CS":"60"}]'
AS
BEGIN
-- Temp table to hold the parsed data
DECLARE @TempTableVariable TABLE(
element_id INT,
sequenceNo INT,
parent_ID INT,
[Object_ID] INT,
[NAME] NVARCHAR(2000),
StringValue NVARCHAR(MAX),
ValueType NVARCHAR(10)
)
-- Parse JSON string into a temp table
INSERT INTO @TempTableVariable
SELECT * FROM parseJSON(@inputJSON)
END
试着看这里：
https://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/
这里有一个完整的ASP.Net项目： http://www.codeproject.com/Articles/788208/Update-Multiple-Rows-of-GridView-using-JSON-in-ASP
- JanBorup
1
10原作者已经给出了答案：https://dev59.com/H3E85IYBdhLWcg3wQxLG#4187412 - janv8000
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接
parseJSON函数查询应该将变量名从parent_id替换为parent_ID，并将string_id替换为string_ID执行。干得好，谢谢 Phil！ - kgzdev