时间戳

首页 > 解决方案 > 如何从 SQL Server 中的 JSON 中提取值列表

sql - 如何从 SQL Server 中的 JSON 中提取值列表

问题描述

在 SQL Server 数据库中，我有一个包含 2 列的表，ID并且JSON. 该JSON列包含 JSON 格式的文本。我可以访问ISJSON、JSON_VALUE和JSON_QUERY，但不能访问OPENJSON。

我正在尝试编写一个类似于下面的查询，它返回 ID 列和所有WorkstationID值。我的查询返回 null。有人可以帮我看看我做错了什么吗？下面是我正在使用的 JSON 数据的示例，以及我的查询，以及我期望输出的样子。感谢您提供的任何帮助！

{
   "areas":[
      {
         "type":"rect",
         "coords":[
            85,
            17,
            175,
            98
         ],
         "href":"#",
         "alt":"0",
         "title":"0",
         "nameid":"592",
         "WorkstationID":"592"
      },
      {
         "type":"rect",
         "coords":[
            214,
            23,
            316,
            97
         ],
         "href":"#",
         "alt":"0",
         "title":"0",
         "nameid":"594",
         "WorkstationID":"594"
      },
      {
         "type":"rect",
         "coords":[
            208,
            132,
            295,
            183
         ],
         "href":"#",
         "alt":"0",
         "title":"0",
         "nameid":"595",
         "WorkstationID":"595"
      },
      {
         "type":"rect",
         "coords":[
            84,
            118,
            179,
            248
         ],
         "href":"#",
         "alt":"0",
         "title":"0",
         "nameid":"596",
         "WorkstationID":"596"
      },
      {
         "type":"rect",
         "coords":[
            83,
            264,
            185,
            322
         ],
         "href":"#",
         "alt":"0",
         "title":"0",
         "nameid":"597",
         "WorkstationID":"597"
      }
   ],
   "img":"/filepath/filename"
}

SELECT ID, JSON_QUERY(JSON,'$.areas') AS WorkstationID
FROM MyTable
WHERE ID = 1

1,592
1,594
1,595
1,596
1,597

更新：正如在下面的评论中所讨论的，OPENJSON 似乎是做到这一点的简单方法，但这对我来说是不可能的，因为我的兼容性级别 < 130。我发现以下正则表达式字符串可用于提取所有来自 JSON 的 WorkstationID。

工作站 ID":"([0-9]{1,4})

使用这样的正则表达式是否有助于以表格格式返回 JSON 中包含的所有 WorkstationID 的列表，就像表格格式中解释的那样？

标签： sqljsonsql-server

解决方案

鉴于这对您来说是一次性的，我编写了一个低效的 JSON 到 XML 转换器供您访问您的数据。

DECLARE @json varchar(8000) =
'{
   "areas":[
      {
         "type":"rect",
         "coords":[85, 17, 175, 98],
         "href":"#",
         "alt":"0",
         "title":"0",
         "nameid":"592",
         "WorkstationID":"592"
      },
      {
         "type":"rect",
         "coords":[214, 23, 316, 97],
         "href":"#",
         "alt":"0",
         "title":"0",
         "nameid":"594",
         "WorkstationID":"594"
      },
      {
         "type":"rect",
         "coords":[208, 132, 295, 183],
         "href":"#",
         "alt":"0",
         "title":"0",
         "nameid":"595",
         "WorkstationID":"595"
      },
      {
         "type":"rect",
         "coords":[84, 118, 179, 248],
         "href":"#",
         "alt":"0",
         "title":"0",
         "nameid":"596",
         "WorkstationID":"596"
      },
      {
         "type":"rect",
         "coords":[83, 264, 185, 322],
         "href":"#",
         "alt":"0",
         "title":"0",
         "nameid":"597",
         "WorkstationID":"597"
      }
   ],
   "img":"/filepath/filename"
}';

DECLARE @Fields table ( field varchar(50), tag varchar(50), term varchar(1), id int IDENTITY(1,1) );
INSERT INTO @Fields VALUES
    ( '"type":"', 'type', '"' ),
    ( '"coords":[', 'coords', ']' ),
    ( '"href":"', 'href', '"' ),
    ( '"alt":"', 'alt', '"' ),
    ( '"title":"', 'title', '"' ),
    ( '"nameid":"', 'nameid', '"' ),
    ( '"WorkstationID":"', 'WorkstationID', '"' ); 

-- Convert JSON to XML --
DECLARE @id int = 1, @field varchar(50), @tag varchar(50), @term varchar(1);
WHILE @id <= ( SELECT MAX ( id ) FROM @Fields )
BEGIN

    -- Current Field --
    SELECT 
        @field = field,
        @tag = tag,
        @term = term
    FROM @Fields WHERE id = @id;

    -- Convert field to XML --
    DECLARE @i int = CHARINDEX ( @field, @json );   
    WHILE @i > 0
    BEGIN
    
        -- Stuff in the opening tag --
        SELECT @json = STUFF (
            @json,
            CHARINDEX ( @field, @json, @i ),
            LEN ( @field ),
            '<' + @tag + '>'
        ),
        @json = STUFF (
            @json,
            CHARINDEX ( @term, @json, CHARINDEX ( @tag, @json, @i ) + 1 ),
            LEN ( @term ),
            '</' + @tag + '>'
        );

        -- Continue field search --
        SET @i = CHARINDEX ( @field, @json, @i );

    END

    -- Next Field --
    SET @id = @id + 1;

END

-- Final Cleanup --
SELECT
    @json = REPLACE ( @json, '{', '<data>' ),
    @json = REPLACE ( @json, '}', '</data>' ),
    @json = REPLACE ( @json, '"areas":[', '<areas>' ),
    @json = REPLACE ( @json, ']', '</areas>' ),
    @json = REPLACE ( @json, '"img":"/filepath/filename"', '<img>/filepath/filename</img>' ),
    @json = REPLACE ( @json, '>,', '>' );

-- Select resultset --
SELECT
    t.f.value( 'type[1]', 'varchar(255)' ) AS [type],
    t.f.value( 'coords[1]', 'varchar(255)' ) AS [coords],
    t.f.value( 'href[1]', 'varchar(255)' ) AS [href],
    t.f.value( 'alt[1]', 'varchar(255)' ) AS [alt],
    t.f.value( 'title[1]', 'varchar(255)' ) AS [title],
    t.f.value( 'nameid[1]', 'varchar(255)' ) AS [nameid],
    t.f.value( 'WorkstationID[1]', 'varchar(255)' ) AS [WorkstationID],
    t.f.value( '../../img[1]', 'varchar(255)' ) AS [img]
FROM (
    SELECT CAST ( @json AS xml ) AS d
) x
CROSS APPLY x.d.nodes('//data/areas/data') t(f);

退货

+------+--------------------+------+-----+-------+--------+---------------+--------------------+
| type |       coords       | href | alt | title | nameid | WorkstationID |        img         |
+------+--------------------+------+-----+-------+--------+---------------+--------------------+
| rect | 85, 17, 175, 98    | #    |   0 |     0 |    592 |           592 | /filepath/filename |
| rect | 214, 23, 316, 97   | #    |   0 |     0 |    594 |           594 | /filepath/filename |
| rect | 208, 132, 295, 183 | #    |   0 |     0 |    595 |           595 | /filepath/filename |
| rect | 84, 118, 179, 248  | #    |   0 |     0 |    596 |           596 | /filepath/filename |
| rect | 83, 264, 185, 322  | #    |   0 |     0 |    597 |           597 | /filepath/filename |
+------+--------------------+------+-----+-------+--------+---------------+--------------------+

您的 JSON 被转换为以下 XML：

<data>
   <areas>
      <data>
         <type>rect</type>
         <coords>85, 17, 175, 98</coords>
         <href>#</href>
         <alt>0</alt>
         <title>0</title>
         <nameid>592</nameid>
         <WorkstationID>592</WorkstationID>
      </data>
      <data>
         <type>rect</type>
         <coords>214, 23, 316, 97</coords>
         <href>#</href>
         <alt>0</alt>
         <title>0</title>
         <nameid>594</nameid>
         <WorkstationID>594</WorkstationID>
      </data>
      <data>
         <type>rect</type>
         <coords>208, 132, 295, 183</coords>
         <href>#</href>
         <alt>0</alt>
         <title>0</title>
         <nameid>595</nameid>
         <WorkstationID>595</WorkstationID>
      </data>
      <data>
         <type>rect</type>
         <coords>84, 118, 179, 248</coords>
         <href>#</href>
         <alt>0</alt>
         <title>0</title>
         <nameid>596</nameid>
         <WorkstationID>596</WorkstationID>
      </data>
      <data>
         <type>rect</type>
         <coords>83, 264, 185, 322</coords>
         <href>#</href>
         <alt>0</alt>
         <title>0</title>
         <nameid>597</nameid>
         <WorkstationID>597</WorkstationID>
      </data>
   </areas>
   <img>/filepath/filename</img>
</data>

推荐阅读