SQL服务器：解析具有不规则值的JSON列

lnlaulya 于 2022-12-15 发布在其他

关注(0)|答案(3)|浏览(121)

我安装了SQL Server 2016（v13），尝试解析包含JSON数据的列。列RequestData中的数据格式如下：

[
    { "Name": "SourceSystem", "Value": "SSValue" },
    { "Name": "SourceSystemId", "Value": "XYZ" }
]
[
    { "Name": "SourceSystemId", "Value": "SSID" },
    { "Name": "SourceSystem", "Value": "SSVALUE2" }
]

我需要获取每行JSON对象的SourceSystem元素的值，下面是我的Select语句：

SELECT TOP 2 
    JSON_VALUE(RequestData, '$[0].Value') AS SourceSystem 
FROM 
    RequestDetail

但是，由于JSON元素在列数据中的顺序，为SourceSystem列返回的值不正确。

SSValue, SSID

请注意，我需要能够解析JSON元素，以便SourceSystem列具有正确的值，即SSValue和SSValue 2。
我也尝试过JSON_Query使用一些在线的例子，但没有运气到目前为止。
谢谢大家！

编辑问题在我发布后已被某人修改，因此我添加此内容以作澄清：正如Question中所给出的，每行数据都将有几个“Name”元素，这些Name元素可以是SourceSystem或SourceSystemId。Question显示数据库表列中两行的数据，但是，正如您所看到的，每行中的SourceID和SourceSystemId元素在第一行和第二行之间的顺序不同。我只需要解析每行的SourceSystem元素。

JSON

来源：https://stackoverflow.com/questions/74754397/sql-server-parsing-a-json-column-with-irregular-values

3条答案

按热度按时间

jdzmm42g1#

使用openjson，获取列中的所有数据，您可以将其用作任何其他表

SELECT
Value
FROM RequestDetail
     CROSS APPLY OPENJSON(RequestDetail.RequestData)
                 WITH (Name nvarchar(20),
  Value nvarchar(20))
WHERE Name = 'SourceSystem';

| 价值|
| - ------|
| SS值|
| SS 2值|
fiddle

赞(0）回复(0）举报 2022-12-15

kgsdhlau2#

假设这里需要OPENJSON，而不是JSON_VALUE：

SELECT *
FROM (VALUES(N'[{"Name":"SourceSystem","Value":"SSValue"},{"Name":"SourceSystemId","Value":"XYZ"}]'),
            (N'[{"Name":"SourceSystemId","Value":"SSID"},{"Name":"SourceSystem","Value":"SSVALUE2"}]'))V(YourJSON)
     CROSS APPLY OPENJSON(V.YourJSON)
                 WITH (Value nvarchar(20));

赞(0）回复(0）举报 2022-12-15

smdnsysy3#

当你想使用JSON_VALUE时，只需选择正确的（需要的）值：

SELECT 
   JSON_VALUE(RequestData, '$[0].Value') AS SourceSystem
FROM RequestDetail

UNION ALL

SELECT 
   JSON_VALUE(RequestData, '$[1].Value') AS SourceSystem
FROM RequestDetail

输出：
| 源系统|
| - ------|
| SS值|
| SSID|
| XYZ|
| SS 2值|
当您只需要“SourceSystem”中的值时，您可以随时执行以下操作：

SELECT SourceSystem
FROM (
   SELECT 
      JSON_VALUE(RequestData, '$[0].Name') AS Name,
      JSON_VALUE(RequestData, '$[0].Value') AS SourceSystem
   FROM RequestDetail

   UNION ALL

   SELECT 
      JSON_VALUE(RequestData, '$[0].Name') AS Name,
      JSON_VALUE(RequestData, '$[1].Value') AS SourceSystem
   FROM RequestDetail )x
WHERE Name='SourceSystem';

输出：
| 源系统|
| - ------|
| SS值|
| XYZ|
参见：DBFIDDLE
编辑：

SELECT 
    x,
    MIN(CASE WHEN Name='SourceSystem' THEN SourceSystem END) as SourceSystem,
    MIN(CASE WHEN Name='SourceSystemId' THEN SourceSystem END) as SourceSystemId
FROM (
   SELECT
      ROW_NUMBER() OVER (ORDER BY RequestData) as x,
      JSON_VALUE(RequestData, '$[0].Name') AS Name,
      JSON_VALUE(RequestData, '$[0].Value') AS SourceSystem
   FROM RequestDetail

   UNION ALL

   SELECT 
      ROW_NUMBER() OVER (ORDER BY RequestData) as x,
      JSON_VALUE(RequestData, '$[1].Name') AS Name,
      JSON_VALUE(RequestData, '$[1].Value') AS SourceSystem
   FROM RequestDetail 
)x
GROUP BY x
;

这将给予：
| x|源系统|源系统ID|
| - ------|- ------|- ------|
| 1个|SS值|XYZ|
| 第二章|SS 2值|SSID|

赞(0）回复(0）举报 2022-12-15

我来回答

SQL服务器：解析具有不规则值的JSON列

3条答案

相关问题

热门标签

最新问答