如何在SQL Server中使用OPENJSON解析BSON日期(unix epoch)

Question

我想将一个JSON文档(由我的python客户端使用json.dumps(payload, default=json_util.default))创建(插入到我的数据库中。我在SQL Server(Azure(中有以下过程。

CREATE PROCEDURE InsertReading(@reading nvarchar(max))
AS BEGIN
insert into Readings(IdMongo, Value, Name, Date)
SELECT _id, value, name, date
FROM OPENJSON (@reading)
WITH (_id nvarchar(50),
value float,
date datetime,
name nvarchar(50))
END  

当我使用这样的数据执行它时，它工作正常(不是我拥有的文档 - 仅测试(：

EXEC InsertReading '{
"value" : 21.625,
"name" : "myname",
"date" : "2016-03-30T07:38:27.102Z"
}'

但它失败了

EXEC InsertReading '{"date": {"$date": 1503074335547}, "value": 23.5, "name": "myname"}'  

错误(日期转换失败并返回 null(：

Cannot insert the value NULL into column 'Date', table 'db.dbo.Readings'; column does not allow nulls. INSERT fails.

如何正确插入该 JSON？最好不要更改客户端中的日期格式(我同时将其发送到 mongo 和 sql(。

Answer 1

是的，这不好玩。SQL Server 不支持此格式，因此您必须手动构造日期。

SELECT _id, [value], [name], 
COALESCE(
[date], 
DATEADD(MILLISECOND, [dateMillis] % 1000, 
DATEADD(SECOND, [dateMillis] / 1000, '19700101'))
)
FROM OPENJSON (@reading)
WITH (_id nvarchar(50),
[value] float,
[date] datetime,
[dateMillis] bigint '$.date."$date"',
[name] nvarchar(50))

这将允许您传递两种格式(日期/时间文本和毫秒偏移量(。