SQL SERVER JSON文本的格式不正确。在0位置找到意外字符
SQL SERVER JSON文本的格式不正确。在0位置找到意外字符
提问于 2022-07-26 04:07:35
很多人都有这个问题，但是这通常是因为JSON是不正确的。有人能告诉我为什么这不管用吗。我希望得到以下结果集：
ONE FirstName LastName
1 John Smith
Instead, I get an empty result set and the following error message in SSMS:
Msg 13609, Level 16, State 2, Line 20
JSON text is not properly formatted. Unexpected character 'J' is found at position 0.
DECLARE @IN_JSON NVARCHAR(MAX) =
N'{
"resume": {
"mailAddress": {
"sectionOrder": 10,
"section": {
"firstName": "John",
"lastName": "Smith"
}
}
}
}'
SELECT '1' AS [One],
JSON_VALUE(a.value, '$.firstName') AS [FirstName],
JSON_VALUE(a.value, '$.lastName') AS [LastName]
FROM OPENJSON (@IN_JSON, '$.resume.mailAddress.section') AS a回答 1
Stack Overflow用户
发布于 2022-07-26 20:30:39
-- A colleague showed me the following which works as well. And thank you again Barbaros for the very fast response.
DECLARE @IN_JSON NVARCHAR(MAX) =
N'{
"resume": {
"mailAddress": {
"sectionOrder": 10,
"section": {
"firstName": "John",
"lastName": "Smith"
}
}
}
}'
SELECT '1' AS [One]
, JSON_VALUE(@IN_JSON, '$.resume.mailAddress.section.firstName') AS [FirstName]
, JSON_VALUE(@IN_JSON, '$.resume.mailAddress.section.lastName') AS [LastName]
-- Output result set:
-- One FirstName LastName
-- 1 John Smith页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/73117629
复制相关文章
相似问题
腾讯云开发者
