我在SQL Server表列中存储了一个JSON字符串。该列名为MSSG_RECIPS，包含一个长文本字符串，例如：
`{"type":"email","recipient":"\"Artzer, Daniel J\" <[email protected]>","sentTS":"2017-11-08T20:58:14.600Z"},{"type":"email","recipient":"\"Friedman, Brian\" <[email protected]>","sentTS":"2017-11-08T20:58:14.600Z"},{"type":"email","recipient":"\"Higgins, Laura L\" <[email protected]>","sentTS":"2017-11-08T20:58:14.600Z"},{"type":"email","recipient":"\"Landenberger, Dan R\" <[email protected]>","sentTS":"2017-11-08T20:58:14.600Z"},{"type":"email","recipient":"\"Leitl, Scott\" <[email protected]>","sentTS":"2017-11-08T20:58:14.600Z"},{"type":"email","recipient":"\"Mendoza, Mario\" <[email protected]>","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)
这将记录返回为行而不是列，但仅返回第一个元素。
可以这么说，我如何向下遍历以检索第二，第三等元素？
最佳答案
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" <[email protected]> | 2017-11-08T20:58:14.600Z |
| 1 | "BBBB, Brian" <[email protected]> | 2017-11-08T20:58:14.600Z |
| 1 | "CCCC, Laura L" <[email protected]> | 2017-11-08T20:58:14.600Z |
| 2 | "xxxx, Daniel J" <[email protected]> | 2017-11-08T20:58:14.600Z |
+----+-------------------------------------------+--------------------------+
注意：存储多元素JSON时，您需要在[]中包含整个JSON
在示例结果ID = 1中有[json-here]，但id = 2没有，请注意结果的不同。返回ID = 1的所有元素，但对于id = 2则不正确。
有关上述示例的实时演示，请参见此dbfiddle。
OPENJSON是一个表值函数，可解析JSON文本并返回
JSON输入中的对象和属性为行和列。在
换句话说，OPENJSON在JSON文档上提供了行集视图。您
可以显式指定行集中的列和JSON属性
用于填充列的路径。由于OPENJSON返回了一组
行，您可以在Transact-SQL的FROM子句中使用OPENJSON
语句，就像您可以使用任何其他表，视图或表值一样
功能。
https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql