json - 解析 SQL Server 中的 JSON 列

Question

我有一个 JSON 字符串存储在 SQL Server 表列中。该列名为 MSSG_RECIPS 并包含一个长文本字符串，例如:

`{"type":"email","recipient":"\"Artzer, Daniel J\" <DJArtzer@emailaddr.com>","sentTS":"2017-11-08T20:58:14.600Z"},{"type":"email","recipient":"\"Friedman, Brian\" <BFriedman@emailaddr.com>","sentTS":"2017-11-08T20:58:14.600Z"},{"type":"email","recipient":"\"Higgins, Laura L\" <LLHiggins@emailaddr.com>","sentTS":"2017-11-08T20:58:14.600Z"},{"type":"email","recipient":"\"Landenberger, Dan R\" <DRLandenberger@emailaddr.com>","sentTS":"2017-11-08T20:58:14.600Z"},{"type":"email","recipient":"\"Leitl, Scott\" <SLeitl@emailaddr.com>","sentTS":"2017-11-08T20:58:14.600Z"},{"type":"email","recipient":"\"Mendoza, Mario\" <MMendoza@emailaddr.com>","sentTS":"2017-11-08T20:58:14.600Z"}`

此示例字符串说明了我的 JSON 格式，每个元素都以逗号分隔。

我能够解析这两种方式。一，使用 JSON_VALUE，我可以检索作为单独列的属性:

select
 JSON_VALUE(mssg_recips, '$.type'),
 JSON_VALUE(mssg_recips, '$.recipient'),
 JSON_VALUE(mssg_recips, '$.sentTS'),
 DOC_ID
from MY_JSON_TAB

但是，这仅返回 JSON 的第一个元素。

我尝试的另一种方法是:

select doc_id, value as EMAIL_RECIP
from MY_JSON_TAB
Cross Apply OPENJSON(MSSG_RECIPS)

这将记录作为行而不是列返回，但同样只返回第一个元素。

可以这么说，我如何向下遍历以检索第二个、第三个等元素？

Answer 1

JSON_VALUE返回单个标量值。对于嵌套 JSON 对象的数组，您可以使用 JSON_QUERY .

使用时CROSS APPLY OPENJSON() WITH( ...)您需要指定要绘制的 JSON 数组的属性，例如

select id, recipient, sentTS
from my_json_tab
CROSS APPLY OPENJSON(my_json) WITH(
       recipient varchar(200)
     , sentTS varchar(60)
     ) as my_json_array

+----+-------------------------------------------+--------------------------+
| id |                 recipient                 |          sentTS          |
+----+-------------------------------------------+--------------------------+
|  1 | "AAAA, Daniel J" <DJArtzer@emailaddr.com> | 2017-11-08T20:58:14.600Z |
|  1 | "BBBB, Brian" <BFriedman@emailaddr.com>   | 2017-11-08T20:58:14.600Z |
|  1 | "CCCC, Laura L" <LLHiggins@emailaddr.com> | 2017-11-08T20:58:14.600Z |
|  2 | "xxxx, Daniel J" <DJArtzer@emailaddr.com> | 2017-11-08T20:58:14.600Z |
+----+-------------------------------------------+--------------------------+

注意:在存储多元素 JSON 时，您需要将整个 JSON 包含在 [] 中

在示例结果 ID=1 有 [ json-here ] 但 id=2 没有，注意结果的差异。返回 ID=1 的所有元素，但对于 id=2 则不然。

看到这个 dbfiddle上面例子的现场演示。

OPENJSON is a table-valued function that parses JSON text and returns objects and properties from the JSON input as rows and columns. In other words, OPENJSON provides a rowset view over a JSON document. You can explicitly specify the columns in the rowset and the JSON property paths used to populate the columns. Since OPENJSON returns a set of rows, you can use OPENJSON in the FROM clause of a Transact-SQL statement just as you can use any other table, view, or table-valued function. https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql