mssql sqlserver openjson函数的用法简介及举例


摘要:
下文讲述openjson函数读取json字符串的方法分享
实验环境:sqlserver 2016



openjson功能说明

OPENJSON行集函数的功能是:将JSON文本转换为一组行和列组成的表集合。
当我们使用openjson返回集合后,我们可以对集合进行sql查询操作,或将集合插入至sqlserver相应结构的表中。
—————————————————————–
openjson语法说明:
OPENJSON( jsonExpression [ , path ] ) [ ]
::= WITH ( { colName type [ column_path ] [ AS JSON ] } [ ,…n ] )
openjson参数说明:
jsonExpression:字符串表达式
path:获取节点的路径
with_clause:指定返回表结构的 类型名及列名
openjson返回值:
表集合



openjson举例应用说明

  ---例1:无with修饰符,返回表集合

  DECLARE @tmp NVARCHAR(MAX)
   SET @tmp='{"keyId":"a","info":"测试数据","qty":88,"listInfo":["A","b","C"]}';
   SELECT * FROM OPENJSON(@tmp);
 /*
Key	        值	             type
keyId	    a	            @shouldalert
info	    测试数据	    @shouldalert
qty	         88	                 2
listInfo	["A","b","C"]	     4
   */

---例2: 
 DECLARE @tmpInfo NVARCHAR(MAX)
SET @tmpInfo =   
  N'[  
       {  
         "CustInfo": {  
           "AccId":"A010212312",  
           "writeDate":"2018-06-07T00:00:00"  
         },  
         "AccountAccId":"C0809001",  
         "List": {  
           "SalePrice":88.65,  
           "QtyInfo":10  
         }  
       },  
       {  
         "CustInfo": {  
           "AccId":"A010212318",  
           "writeDate":"2018-06-02T00:00:00"  
         },  
         "AccountAccId":"C0809002",  
         "List": {  
           "SalePrice":90.3,  
           "QtyInfo":4  
         }  
      }  
 ]'  

SELECT * FROM   OPENJSON (@tmpInfo)  
WITH (   
              AccId          varchar(200)   '$.CustInfo.AccId' ,  
              writeDate      datetime       '$.CustInfo.writeDate',  
              Customer       varchar(200)   '$.AccountAccId',  
              QtyInfo         int           '$.List.QtyInfo'  
 ) 
-----输出--------------
AccId	          writeDate	      Customer	           QtyInfo
A010212312	2018-06-07T00:00:00	C0809001	   10
A010212318	2018-06-02T00:00:00	C0809002	    4


注意事项:
OPENJSON 需数据库的兼容性级别为130,会大于130,否则将无法找到此内置函数
修改数据库兼容性的脚本如下:
ALTER DATABASE <数据库名称> SET COMPATIBILITY_LEVEL = 130