SQL Sever Josn相互转化

正向转化：

 SELECT TOP 2 StudentID, Name AS "name", Sex AS "urname"
 FROM dbo.student
 FOR JSON PATH ,ROOT('Student');

 

数据展示：

{"STUdentID":"STU12021A0000001","UserName":"201740450101","PassWord":"666666","StuName":"李1","StuSex":"男","Stuaddress":"贵州省","StuPhone1":"17771198523","StuPhone2":"17678475960","Creadate":"2021-10-11T16:28:43.180"},
 {"STUdentID":"STU12021A0000002","UserName":"201740450102","PassWord":"666666","StuName":"李2","StuSex":"男","Stuaddress":"贵州省","StuPhone1":"17771198523","StuPhone2":"1767895060","Creadate":"2021-10-11T16:28:58.920"}
 ]}';

 

解析

  SELECT STUdentID, UserName, PassWord, StuName,StuSex,Stuaddress,StuPhone1,StuPhone2,Creadate
  FROM OPENJSON (@ss, '$.TestRoot')
  WITH (
  STUdentID varchar(200),
  UserName varchar(200),
  PassWord varchar(200),
  StuName varchar(200),
  StuSex varchar(200),
  Stuaddress varchar(200),
 StuPhone1 varchar(200),
 StuPhone2 varchar(200),
 Creadate DATETIME
 ) AS TestRoot

 

 能帮朋友解决问题的记得给个关注支持一下，以后将多多分享SQL SERVER 相关知识