时间戳

首页 > 解决方案 > Json_value 和 Json_query 找不到指定路径

sql - Json_value 和 Json_query 找不到指定路径

问题描述

我正在尝试解析来自以下 JSON 的数据。我知道 JSON_VALUE 和 JSON_QUERY 之间存在差异，但我试图让路径结构正确。我正在使用 STRICT 选项来验证我的路径，到目前为止，除了用于 JSON Query 的 string$ 之外，一切都因找不到路径而失败。一旦我添加了 .data.taskData ，路径似乎就会爆炸。任何帮助将不胜感激。

我将以下 JSON 设置为 @json

declare @json nvarchar(max)
SELECT JSON_VALUE(@json, 'strict$.data.taskData.startedLocation') as json 
select JSON_QUERY(@json, 'strict$.data.taskData.startedLocation')  as json

下面是我试图解析的 JSON

{"data.taskData":{"startedAtUtc":"2019-08-28T20:21:29.025Z","startedLocation":{"lat":60.7348366,"lon":-124.9856841},"additionalData":[],"bols":[{"number":"1234","product":{"id":"COFFEE","description":"GROUND COFFE 5LB CAN","plannedQuantity":1352,"uom":"PCS","supplier":"WALMART ","accountOf":"","class":"UNKNOWN","loadedQuantity":6600,"netQuantity":9993},"net":"9993"}],"compartments":[{"id":"1","capacity":3400,"commodity":null,"consignee":"KSUAC","plannedQuantity":0,"tankID":"1","additionalData":[],"allLoadsValid":true,"complete":true,"error":false,"locked":false,"loads":[{"isFirst":true,"quantity":"1000","bol":"1234"}],"loadedQuantity":1000,"productID":"COFFEE"},{"id":"2","capacity":2000,"commodity":null,"consignee":"KSUAC","plannedQuantity":0,"tankID":"2","additionalData":[],"allLoadsValid":true,"complete":true,"error":false,"locked":false,"loads":[{"isFirst":true,"quantity":"2000","bol":"1234"}],"loadedQuantity":2000,"productID":"COFFEE"},{"id":"3","capacity":1100,"commodity":null,"consignee":"KSUAC","plannedQuantity":0,"tankID":"3","additionalData":[],"allLoadsValid":true,"complete":true,"error":false,"locked":false,"loads":[{"isFirst":true,"quantity":"1100","bol":"1234"}],"loadedQuantity":1100,"productID":"COFFEE"},{"id":"4","capacity":2700,"commodity":null,"consignee":null,"plannedQuantity":0,"tankID":"4","additionalData":[],"allLoadsValid":true,"complete":true,"error":false,"locked":false,"loads":[{"isFirst":true,"quantity":"2500","bol":"1234"}],"loadedQuantity":2500,"productID":"COFFEE"}],"detention":{"minutes":null,"reasonCode":null,"notes":null},"initialCompartments":[{"id":"1","capacity":3400,"commodity":null,"consignee":null,"plannedQuantity":null,"tankID":"1"},{"id":"2","capacity":2000,"commodity":null,"consignee":null,"plannedQuantity":null,"tankID":"2"},{"id":"3","capacity":1100,"commodity":null,"consignee":null,"plannedQuantity":null,"tankID":"3"},{"id":"4","capacity":2700,"commodity":null,"consignee":null,"plannedQuantity":null,"tankID":"4"}],"loadingComplete":"yes","loadingCompleteTime":"2019-08-28T20:23:05.453Z","uom":{"key":"PCS","category":"volume","shortDisplay":"p","longDisplay":"Pieces","conversionFactors":{"gal":0.0625,"L":0.2365882365,"c":4.2267528377}},"variances":[],"completedAtUtc":"2019-08-28T20:23:06.703Z","completedLocation":{"lat":61.7348308,"lon":-124.9856879},"finalCompartments":[{"id":"1","capacity":3400,"productID":"COFFEE","loadedQuantity":1000,"consignee":"KSUAC","tankID":"1"},{"id":"2","capacity":2000,"productID":"COFFEE","loadedQuantity":2000,"consignee":"KSUAC","tankID":"2"},{"id":"3","capacity":1100,"productID":"COFFEE","loadedQuantity":1100,"consignee":"KSUAC","tankID":"3"},{"id":"4","capacity":2700,"productID":"COFFEE","loadedQuantity":2500,"consignee":null,"tankID":"4"}]}}

标签： sqljsonsql-servertsql

解决方案

这有效：

select JSON_QUERY(@json, 'strict$."data.taskData".startedLocation')  as json
SELECT JSON_VALUE(@json, 'strict$."data.taskData".startedLocation.lat') as json

推荐阅读