sqlserver2016版添加的json操作

@json nvarchar ( max ) = ‘ { "id": 1, "name": "ki", "age": 22, "son": { "name": "son", "age": 1 }, "list": [ { "city": "上海", "area": "松江" }, { "city": "上海", "area": "松江" } ] } ‘ ;

 

一、isjson实现json验证

 --  结果：1-json格式正确；0-json格式错误 
 declare   @json1   nvarchar ( max )  =   ‘  {"id":1,"name":"ki","age":22,"son":{"name":"son","age":1},"list":[{"city":"上海","area":"松江"},{"city":"上海","area":"松江"}]}  ‘ 
 print  isjson( @json1 )

结果：1

二、JSON_VALUE取出json值

 declare   @json2   nvarchar ( max )  =   ‘  {"id":1,"name":"ki","age":22,"son":{"name":"son","age":1},"list":[{"city":"上海","area":"松江"},{"city":"上海","area":"松江"}]}  ‘ 
 select  JSON_VALUE( @json2 , ‘  $.name  ‘ )

结果：ki

三、JSON_QUERY取出json中的对象和数组

 declare   @json3   nvarchar ( max )  =   ‘  {"id":1,"name":"ki","age":22,"son":{"name":"son","age":1},"list":[{"city":"上海","area":"松江"},{"city":"上海","area":"松江"}]}  ‘ 
 select  JSON_QUERY( @json3 , ‘  $.son  ‘  )
  select  JSON_QUERY( @json3 , ‘  $.list  ‘ )

结果：

结果1：{"name":"son","age": 1  }
结果2：  [  {"city":"上海","area":"松江"},{"city":"上海","area":"松江"}  ] 

 

四、json字符串转化成表格式

 --  如果没有数组，可以将OUTER APPLY OPENJSON() WITH()省略 
 declare   @json4   nvarchar ( max )  =   ‘  {"id":1,"name":"ki","age":22,"son":{"name":"son","age":1},"list":[{"city":"上海","area":"松江"},{"city":"上海","area":"松江"}]}  ‘ 

 select   *   from  openjson( @json4  )
  with  (
    id   nvarchar ( 500 )  ‘  strict $.id  ‘ , --  strict表示json中必须包含该字段 
    name  nvarchar ( 500 )  ‘  strict $.name  ‘  ,
    age   nvarchar ( 500 )  ‘  strict $.age  ‘  ,
    son_name   nvarchar ( 500 )  ‘  strict $.son.name  ‘  ,
    son_age   nvarchar ( 500 )  ‘  strict $.son.age  ‘  ,
    list   nvarchar ( max )  ‘  $.list  ‘   AS  JSON --  必须是nvarchar(max) 类型 
 )
  OUTER   APPLY OPENJSON(list)
    WITH  (
            city   varchar ( 80 )  ‘  $.city  ‘  ,
            area   varchar ( 80 )  ‘  $.area  ‘  
            ); 

结果：

 

 

 

五、将表格式转化成json格式

 --  for json auto 安装原列名自动转化  
--  for json path 指定输出json的key名称,并可以控制输出字段  
--   for json auto,WITHOUT_ARRAY_WRAPPER    跟在for json auto/path后可以删除中括号 
 select   *   from  student  for   json auto
  select   *   from  student  for   json auto,WITHOUT_ARRAY_WRAPPER
  select   
         id   as   uniqueId,
         name,
         name   as   ‘  info.name  ‘ 
          from  student  for  json path

结果：

结果： 1  [  {"id":1,"name":"ki","age":34,"create_time":"2020-09-01T11:12:43.230"},{"id":2,"name":"李四","age":33,"create_time":"2020-09-01T11:12:43.230"},{"id":3,"name":"ki","age":34,"create_time":"2020-09-01T11:12:50.987"},{"id":4,"name":"李四a","age":33,"create_time":"2020-09-01T11:12:50.987"}  ]  
结果2：{"id":  1 ,"name":"ki","age": 34 ,"create_time":" 2020  -  09  - 01T11: 12 : 43.230 "},{"id": 2 ,"name":"李四","age": 33 ,"create_time":" 2020  -  09  - 01T11: 12 : 43.230 "},{"id": 3 ,"name":"ki","age": 34 ,"create_time":" 2020  -  09  - 01T11: 12 : 50.987 "},{"id": 4 ,"name":"李四a","age": 33 ,"create_time":" 2020  -  09  - 01T11: 12 : 50.987  "}
结果3：  [  {"uniqueId":1,"name":"ki","info":{"name":"ki"}},{"uniqueId":2,"name":"李四","info":{"name":"李四"}},{"uniqueId":3,"name":"ki","info":{"name":"ki"}},{"uniqueId":4,"name":"李四a","info":{"name":"李四a"}}  ] 

 student表数据：

 /*  
 Navicat Premium Data Transfer

 Source Server         : localSqlServer
 Source Server Type    : SQL Server
 Source Server Version : 14001000
 Source Host           : 127.0.0.1:1433
 Source Catalog        : custom
 Source Schema         : dbo

 Target Server Type    : SQL Server
 Target Server Version : 14001000
 File Encoding         : 65001

 Date: 19/01/2021 17:03:20
  */ 


 --   ----------------------------  
--   Table structure for student  
--   ---------------------------- 
 IF   EXISTS  ( SELECT   *   FROM  sys.all_objects  WHERE   object_id   =   OBJECT_ID (N ‘  [dbo].[student]  ‘ )  AND  type  IN  ( ‘  U  ‘  ))
      DROP   TABLE   [  dbo  ] . [  student  ] 
 GO 

 CREATE   TABLE   [  dbo  ] . [  student  ]   (
    [  id  ]   int    IDENTITY ( 1 , 1 )  NOT   NULL  ,
    [  name  ]   varchar ( 5 ) COLLATE Chinese_PRC_CI_AS   NULL  ,
    [  age  ]   int    NULL  ,
    [  create_time  ]   datetime    NULL  
)
  GO 

 ALTER   TABLE   [  dbo  ] . [  student  ]   SET  (LOCK_ESCALATION  =   TABLE  )
  GO 


 --   ----------------------------  
--   Records of student  
--   ---------------------------- 
 SET   IDENTITY_INSERT   [  dbo  ] . [  student  ]   ON 
 GO 

 INSERT   INTO   [  dbo  ] . [  student  ]  ( [  id  ] ,  [  name  ] ,  [  age  ] ,  [  create_time  ] )  VALUES  (N ‘  1  ‘ , N ‘  ki  ‘ , N ‘  34  ‘ , N ‘  2020-09-01 11:12:43.230  ‘  )
  GO 

 INSERT   INTO   [  dbo  ] . [  student  ]  ( [  id  ] ,  [  name  ] ,  [  age  ] ,  [  create_time  ] )  VALUES  (N ‘  2  ‘ , N ‘  李四  ‘ , N ‘  33  ‘ , N ‘  2020-09-01 11:12:43.230  ‘  )
  GO 

 INSERT   INTO   [  dbo  ] . [  student  ]  ( [  id  ] ,  [  name  ] ,  [  age  ] ,  [  create_time  ] )  VALUES  (N ‘  3  ‘ , N ‘  ki  ‘ , N ‘  34  ‘ , N ‘  2020-09-01 11:12:50.987  ‘  )
  GO 

 INSERT   INTO   [  dbo  ] . [  student  ]  ( [  id  ] ,  [  name  ] ,  [  age  ] ,  [  create_time  ] )  VALUES  (N ‘  4  ‘ , N ‘  李四a  ‘ , N ‘  33  ‘ , N ‘  2020-09-01 11:12:50.987  ‘  )
  GO 

 SET   IDENTITY_INSERT   [  dbo  ] . [  student  ]   OFF 
 GO 


 --   ----------------------------  
--   Auto increment value for student  
--   ---------------------------- 
 DBCC  CHECKIDENT ( ‘  [dbo].[student]  ‘ , RESEED,  6  )
  GO 


 --   ----------------------------  
--   Primary Key structure for table student  
--   ---------------------------- 
 ALTER   TABLE   [  dbo  ] . [  student  ]   ADD   CONSTRAINT   [  PK__student__3213E83F18AD2C2B  ]   PRIMARY   KEY   CLUSTERED  ( [  id  ]  )
  WITH  (PAD_INDEX  =   OFF , STATISTICS_NORECOMPUTE  =   OFF , IGNORE_DUP_KEY  =   OFF , ALLOW_ROW_LOCKS  =   ON , ALLOW_PAGE_LOCKS  =   ON  )  
  ON   [  PRIMARY  ] 
 GO 

 

sqlserver2016版添加的json操作

标签：rap   student   alt   drop   primary   没有   pen   app   tran   

查看更多关于sqlserver2016版添加的json操作的详细内容...