留言13607,16层，状态4,27行

Question

我在这里很新的，我试图解析我的JSON，但不断得到这个错误。我有以下查询，每次我尝试运行它，我得到一个错误，任何机会我可以得到一些帮助?我的预期结果是将数据显示在一个普通的sql网格上，然后将数据插入到一个具有以下格式的表中。

Declare @json varchar(MAX) = '
{"stops": 
{
"__type": "stop",
"__name": "stops",
"company_id": "xxx",
"actual_arrival": "20210910130000-0500",
"actual_departure": "20210910140000-0500",
"address": "123 noneyabizwax",
"appt_required": false,
"city_id": 144537,
"city_name": "LEWISBURG",
"state": "TN",
"zip_code": "37091",
"confirmed": false,
"driver_load_unload": "N",
"eta": "20210910122900-0500",
"eta_distance": 46,
"eta_late": false,
"eta_oor": false,
"order_id": "12345",
"planned_arrival_time": "20210910122900-0500",
"sched_arrive_early": "20210910130000-0500",
"status": "D",
"stop_type": "PU"
}';
select *
from OPENJSON ( @json )             
With (          
Type Varchar(50) '$stops.__type',
Name Varchar(50) '$stops.__name',
CompID Varchar(100) '$stops.company_id' ,
AcArrvl DATETIME '$stops.actual_arrival' ,
AcDprtr DATETIME '$stops.actual_departure',
Addrs Varchar (200) '$stops.address',
Appt Varchar(50) '$stops.appt_required',
CityID Varchar(50) '$stops.city_id',
City Varchar(200) '$stops.city_name',
State nvarchar(max) '$stops.state',
Zip int '$stops.zip_code',
Confirmed Varchar(50) '$stops.confirmed',
DrvrLd nvarchar(max) '$stops.driver_load_unload',
ETA DATETIME '$stops.eta',
ETADISTNC INT '$stops.eta_distance',
ETALate nvarchar(max) '$stops.eta_late',
ETAOOR nvarchar(max) '$stops.eta_oor',
OrderID INT '$stops.order_id',
PlnndArrvlTm DATETIME '$stops.planned_arrival_time',
SchdArrvEarly DATETIME '$stops.sched_arrive_early',
Status nvarchar(max) '$stops.status',
StopType Varchar(50) '$stops.stop_type',
[Stops] nvarchar(max) AS JSON
) 

Answer 1

Declare @json varchar(MAX) = '
{"stops": 
{
"__type": "stop",
"__name": "stops",
"company_id": "xxx",
"actual_arrival": "20210910130000-0500",
"actual_departure": "20210910140000-0500",
"address": "123 noneyabizwax",
"appt_required": false,
"city_id": 144537,
"city_name": "LEWISBURG",
"state": "TN",
"zip_code": "37091",
"confirmed": false,
"driver_load_unload": "N",
"eta": "20210910122900-0500",
"eta_distance": 46,
"eta_late": false,
"eta_oor": false,
"order_id": "12345",
"planned_arrival_time": "20210910122900-0500",
"sched_arrive_early": "20210910130000-0500",
"status": "D",
"stop_type": "PU"}
}';
WITH date_convert 
AS
(
SELECT  CONVERT(DATETIMEOFFSET,STUFF(STUFF(STUFF(STUFF(STUFF(STUFF(JSON_VALUE(@JSON, '$.stops.actual_arrival'),18,0,':'),13,0,':'),11,0,':'),9,0,' '),7,0,'-'),5,0,'-')) AcArrvl,
CONVERT(DATETIMEOFFSET,STUFF(STUFF(STUFF(STUFF(STUFF(STUFF(JSON_VALUE(@JSON, '$.stops.actual_departure'),18,0,':'),13,0,':'),11,0,':'),9,0,' '),7,0,'-'),5,0,'-')) AcDprtr,
CONVERT(DATETIMEOFFSET,STUFF(STUFF(STUFF(STUFF(STUFF(STUFF(JSON_VALUE(@JSON, '$.stops.eta'),18,0,':'),13,0,':'),11,0,':'),9,0,' '),7,0,'-'),5,0,'-')) ETA,
CONVERT(DATETIMEOFFSET,STUFF(STUFF(STUFF(STUFF(STUFF(STUFF(JSON_VALUE(@JSON, '$.stops.planned_arrival_time'),18,0,':'),13,0,':'),11,0,':'),9,0,' '),7,0,'-'),5,0,'-')) PlnndArrvlTm,
CONVERT(DATETIMEOFFSET,STUFF(STUFF(STUFF(STUFF(STUFF(STUFF(JSON_VALUE(@JSON, '$.stops.sched_arrive_early'),18,0,':'),13,0,':'),11,0,':'),9,0,' '),7,0,'-'),5,0,'-')) SchdArrvEarly
)
select Type, Name,CompID,date_convert.AcArrvl,date_convert.AcDprtr,Addrs,Appt,CityID,City,State,Zip,Confirmed,DrvrLd,date_convert.ETA,ETADISTNC,ETALate,ETAOOR,OrderID,date_convert.PlnndArrvlTm,
date_convert.SchdArrvEarly, Status,StopType
from date_convert,
OPENJSON (@json)
With (          
Type Varchar(50) '$.stops.__type',
Name Varchar(50) '$.stops.__name',
CompID Varchar(100) '$.stops.company_id' ,
Addrs Varchar (200) '$.stops.address',
Appt Varchar(50) '$.stops.appt_required',
CityID Varchar(50) '$.stops.city_id',
City Varchar(200) '$.stops.city_name',
State nvarchar(max) '$.stops.state',
Zip int '$.stops.zip_code',
Confirmed Varchar(50) '$.stops.confirmed',
DrvrLd nvarchar(max) '$.stops.driver_load_unload',
ETADISTNC INT '$.stops.eta_distance',
ETALate nvarchar(max) '$.stops.eta_late',
ETAOOR nvarchar(max) '$.stops.eta_oor',
OrderID INT '$.stops.order_id',
Status nvarchar(max) '$.stops.status',
StopType Varchar(50) '$.stops.stop_type'
)

看起来有点复杂，但我在下面做了解释:

1. 在本行末尾添加}(花括号)"stop_type": "PU"
2. 您必须在With条款的每个$符号之后添加.(点)。
然后我们需要删除[Stops] nvarchar(max) AS JSON行，因为你不需要打开另一个JSON对象，也不要忘记在StopType Varchar(50) '$.stops.stop_type,'之后删除,(逗号)
3. 我们需要将您的5个日期字符串转换为至少部分人类可读的格式。为此，我们需要使用CONVERT()和STUFF()函数。我可以在SELECT语句中转换所有5个字符串，但因为它看起来很丑，我决定在CTE中这样做(我将CTE命名为date_convert)
4. 因为我们将转换后的日期存储在CTE中，所以在With子句中不需要它们。所以我们删除它们。
5. 为了在一个表中看到我们与其他列转换的日期，我们需要将CTE的名称添加到FROM子句中。完成后，我们必须从With子句和CTE到SELECT语句中添加每个列的名称。订单并不重要，但我保留了你最初问题中的订单。

https://dbfiddle.uk/?rdbms=sqlserver_2016&小提琴= 5 b3915d8df86127e1f3c84d0a9880e31