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

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

转载作者：行者123 更新时间：2023-12-02 04:23:09

我正在尝试解析来自以下 JSON 的数据。我知道 JSON_VALUE 和 JSON_QUERY 之间存在差异，但我只是想使路径结构正确。我正在使用 STRICT 选项来验证我的路径，到目前为止，除了 string$ 之外，对于 JSON 查询，一切都因找不到路径而失败。一旦我添加 .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"}]}}

最佳答案

试试这个。在代码之后我将评论特色:

DECLARE @json NVARCHAR(MAX)=N'{
    "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"
            }
        ]
    }
}';

--查询

SELECT FirstLevel.StartedAtUtc
      ,JSON_VALUE(FirstLevel.startedLocation,'$.lat') AS startedLocation_Lat
      ,JSON_VALUE(FirstLevel.startedLocation,'$.lon') AS startedLocation_Lon
      ,FirstLevel.additionalData
      ,FirstLevel.bols
      ,Sub_Compartments.id
      ,Sub_Compartments.capacity
      ,FirstLevel.loadingComplete
FROM OPENJSON(@json,'$."data.taskData"')
WITH(startedAtUtc DATETIME2
    ,startedLocation NVARCHAR(MAX) AS JSON
    ,additionalData NVARCHAR(MAX) AS JSON
    ,bols NVARCHAR(MAX) AS JSON
    --compartments seems to be a 1:n related node
    ,compartments NVARCHAR(MAX) AS JSON
    ,loadingComplete NVARCHAR(10)
    --and more
    ) FirstLevel
OUTER APPLY OPENJSON(FirstLevel.compartments)
WITH (id INT
     ,capacity INT
     --more columns
     ) Sub_Compartments;

简而言之:

您的 JSON 是一个完全成熟的深层嵌套结构，包含各种数据。返回整体和所有会导致非常多余的平面表。使用尽可能窄的问题查询此 JSON 是个好主意。

正如您已经被告知的，我们可以使用其中之一:

JSON_VALUE() 从给定路径检索标量值。嵌套的 JSON 将作为字符串类型返回。
JSON_QUERY() 用于提取 JSON 片段并将其作为 JSON 处理
需要 OPENJSON 来深入研究具有重复元素的 JSON，以便将片段作为派生集逐行返回。非常重要的是 WITH 子句，它允许将 OPENJSON 的默认输出更改为列的并排(类似于 PIVOT)。

在这种情况下，我们可以使用 OPENJSON 深入到第一层并返回在那里找到的对象。其中一些是标量值，可以作为类型值返回，其他是 JSON 对象。在这种情况下，我们必须使用 NVARCHAR(MAX) 作为数据类型，并且必须将 sepcify AS JSON 以便继续处理返回值。

在您的 JSON 中，隔间是 1:n 相关集。我们可以使用级联的 OPENJSON 调用，使用第一个返回的片段作为输入，并使用另一个 WITH 子句来提取隔间的列。

我希望这个例子能给你足够的提示，让你可以查询 JSON 的任何地方。祝你好运!

关于sql - Json_value 和 Json_query 找不到指定的路径，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/57778767/

文章推荐： youtube-api - 如何配置本地主机以从 Youtube API 请求数据？

文章推荐： apache-spark - 如何遍历pyspark中的每一行dataFrame

java - 指定 "other"
我正在我的 java 作业中使用 GUI，并且我必须指定 JCheckBox 中的其他内容。除了这个小要求，其他的我都完成了。我不太确定如何解决这个问题，我查阅了我的书并尝试在线研究要求: 一系列复
javascript - 指定 for 循环的终点有哪些优点和缺点？
在各种语言中(我将在这里使用 JavaScript，但我已经在 PHP 和 C++ 中以及可能在其他地方看到过它)，似乎有几种构造简单 for 循环的方法。版本 1 如下: var top = doc
javascript - css 指定 < >
有没有一种方法可以使用 CSS 指定每次“小于符号”(在键盘上 M 的右侧)或“大于符号”出现在文本中时，它应该被替换为分别是“小于”或“大于”的实际词？最佳答案 CSS 不能作用于(不能修改，即)
kerberos - 指定 SPN 的正确格式是什么？
首先，使用 setspn 命令为用户注册服务主体名称。 setspn -a CS/dummy@abc.com dummyuser setspn -l dummyuser 给出输出为 CS/dummy@
javascript - 指定 SFSafariViewController 用户代理
我在指定从 SFSafariViewController 访问时遇到问题，因为它具有与 Safari 浏览器完全相同的用户代理。我要做的是仅在 webview 内显示图片，如果在普通浏览器上查看，则
r - 指定 CFA，其中湍流是外生相关性的总和
我正在尝试用 R 语言在 lavaan 中指定一个奇怪的模型。该模型如下所示: 我的规范尝试如下所示。我发现难以实现的是将观察到的变量的唯一误差固定为唯一项的两个相关性的总和。例如，项目 y*1,2
reactjs - 指定 axios 响应数据类型
我正在构建 API 以将我的 React 应用程序与我的后端服务连接起来，我想使用 typescript 来指定 data 的类型在我的 Axios 请求中。如何在不修改其他字段的情况下更新 Axio
z3 - 指定 Z3 的初始模型值
如何为模型指定初始“软”值？该初始模型是解决类似查询的结果，并且该模型很可能具有正确的部分，甚至对于当前查询可能是正确的。目前，我正在通过增量求解和 hard/soft constraints 对此
java - 指定 Kafka 生产者的分区数
我有来自网页的以下代码 https://cwiki.apache.org/confluence/display/KAFKA/0.8.0+Producer+Example 似乎缺少的是如何配置分区数。我
Neo4jClient:指定 Cypher 解析器版本？
有没有办法在每个查询的基础上在 Neo4jClient 中指定 Cypher 解析器的版本，如 here 所述? 谢谢! 最佳答案如果您将 Neo4jClient 更新到最新版本(> 1.0.0.6
r - 指定 `curve` 绘图高度？
我有以下代码生成四个图，但它们最终被压扁(见下图)。我该如何解决这个问题？ par(mfrow=c(2,2)) curve(.5*exp(-.5*x),from=0,to=10,main="f(x)"
coldfusion - 指定 JDBC 数据库驱动程序最大线程数
我有一个 ColdFusion 10 服务器。我正在使用 JDBC 驱动程序连接到 db2 数据库。我偶然发现了这个笔记。这个设置在哪里？我还查看了 neo*.xml 文件，但没有看到任何 db 驱动
jquery - 指定 jQuery 验证插件中验证器的顺序
我想知道是否可以指定验证器的运行顺序。目前，我编写了一个自定义验证器，检查它是否为 [a-zA-Z0-9]+ 以确保登录验证我们的规则，并编写了一个远程验证器以确保登录可用，但目前远程验证器已启动在
iphone - 指定 iPhone 应用程序的最低内存要求
我的应用程序需要至少 40MB 的 RAM，因此早期的 iPhone(例如 3G、第一个 iPod touch 版本)就没有它(它们为我的应用程序提供的最大内存约为 20MB)。有没有正确的方法来禁用
java - 指定 ZonedDateTime 的时区而不更改实际日期
我有一个保存日期(不是当前日期)的 Date 对象，我需要以某种方式指定该日期为 UTC，然后将其转换为“欧洲/巴黎”，即 +1 小时。 public static LocalDateTime toL
caching - 指定 varnish 后端而不缓存
我想问你在 Varnish 代码中如何在没有缓存的情况下将请求传递到后端。我知道我可以做到并且正在发挥作用: if (req.url ~ "(\?|&)(something|somethin
module - 指定 gfortran 应在其中查找模块的目录
我目前基于模块编译程序(如主程序 foo 依赖于模块 bar )如下: gfortran -c bar.f90 gfortran -o foo.exe foo.f90 bar.o 这在 foo.f90
javascript - 指定 Meteor 包依赖项的正确方法
我正在尝试创建一个依赖于另一个 meteor 包的新 meteor 包。当我尝试 meteor add mypackage 时，出现以下错误。为什么 Meteor 不添加 mypackage 并引入它
rust - 指定 Rust 闭包生命周期
我正在制作执行器/ react 器，同时发现这是一个终生的问题。它与 async/Future 无关，可以在没有 async 糖的情况下进行复制。 use std::future::Future; s
cassandra - 指定 cqlsh 输出时区
我在 cassandra 中有一个表，其数据类型为时间戳。我正在使用 cqlsh 从数据库中获取数据，并希望更改我的时间戳列输出的输出格式。我研究了一下，发现我可以通过更改以下文件来更改时间戳输出格式

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

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