当前位置：首页 > article >正文

MS SQL Table与JSON转换

article 2024/9/22 22:21:40

1.表转换JSON

1.1案例

案例1

 create table #t2
 ([User_id] varchar(20),
 [PWS]   VARCHAR(40)
 )

 INSERT INTO #t2
 SELECT 'ADMIN','12345'
 UNION
  SELECT 'INPUTUSER','YTETS'


  select * from #t2 for json path, without_array_wrapper
  select * from #t2 for json path

运行效果：

{"User_id":"ADMIN","PWS":"12345"},{"User_id":"INPUTUSER","PWS":"YTETS"}

[{"User_id":"ADMIN","PWS":"12345"},{"User_id":"INPUTUSER","PWS":"YTETS"}]

1.2语法解析

若要删除默认括住 FOR JSON 子句的 JSON 输出的方括号，请指定 WITHOUT_ARRAY_WRAPPER 选项。将此选项用于单行结果，生成单个 JSON 对象作为输出，而不是生成具有单个元素的数组

2.JSON转换表

2.1案例

案例2

create table #t
(id int,
json_date nvarchar(max))

--delete from #t

insert into #t
select 1,'{"User_id":"admin","PSW":"123"}'
insert into #t
select 2,'{"User_id":"INPUTUSER","PSW":"YTETS"}'

 select   * from #t
 cross apply OPENJSON(json_date)
  select  id,
   case when isjson(json_date)=1 then JSON_VALUE(json_date,'$.User_id') end [User_id], 
  case when isjson(json_date)=1 then  JSON_VALUE(json_date,'$.PSW')end PSW
   from #t
  select   * from #t  
 cross apply OPENJSON(json_date)
 WITH (   
              [User_id]   VARCHAR(200)   '$.User_id',  
              PWS     VARCHAR(200)       '$.PWS'
 )

运行效果：

在上述基础上，增加一下语句：

案例3

insert into #t
select 3,'{"User_id":"ADMIN","PWS":"12345"},{"User_id":"INPUTUSER","PWS":"YTETS"}'
 select   * from #t  
 cross apply OPENJSON(json_date)
  select  id,
		case when isjson(json_date)=1 then JSON_VALUE(json_date,'$.User_id') end [User_id], 
		case when isjson(json_date)=1 then  JSON_VALUE(json_date,'$.PSW')end PSW
   from #t

运行效果：

对于id为3无法达到预期效果。如何解？请参考案例4

案例4

DECLARE @JSON NVARCHAR(MAX)  
SET  @JSON=N'[{"User_id":"ADMIN","PWS":"12345"},{"User_id":"INPUTUSER","PWS":"YTETS"}]'
--SET  @JSON=N'[{"User_id":"INPUTUSER","PSW":"YTETS"}]'
SELECT root.[key] AS RID,TheValues.[key], TheValues.[value]
FROM OPENJSON ( @JSON ) AS root
CROSS APPLY OPENJSON ( root.value) AS TheValues

运行结果：

案例4达到预期，但是JSON一定要带“方括号”

官方案例参考：

案例5

DECLARE @json NVARCHAR(MAX) = N'[  
  {  
    "Order": {  
      "Number":"SO43659",  
      "Date":"2011-05-31T00:00:00"  
    },  
    "AccountNumber":"AW29825",  
    "Item": {  
      "Price":2024.9940,  
      "Quantity":1  
    }  
  },  
  {  
    "Order": {  
      "Number":"SO43661",  
      "Date":"2011-06-01T00:00:00"  
    },  
    "AccountNumber":"AW73565",  
    "Item": {  
      "Price":2024.9940,  
      "Quantity":3  
    }  
  }
]'  
   
SELECT *
FROM OPENJSON ( @json )  
WITH (   
              Number   VARCHAR(200)   '$.Order.Number',  
              Date     DATETIME       '$.Order.Date',  
              Customer VARCHAR(200)   '$.AccountNumber',  
              Quantity INT            '$.Item.Quantity',  
              [Order]  NVARCHAR(MAX)  AS JSON  
 )

2.2语法解析

1：apply有两种形式： cross apply 和 outer apply

CROSS APPLY仅返回外部表中通过表值函数生成结果集的行。OUTER APPLY既返回生成结果集的行，也返回不生成结果集的行，其中表值函数生成的列中的值为 NULL

语法：<left_table_expression> {cross|outer} apply <right_table_expression>

2.OPENJSON 是一种表值函数，可分析 JSON 文本，并以行和列的形式从 JSON 输入返回对象和属性。换句话说，OPENJSON 对 JSON 文档提供行集视图。可以显式指定行集中的列以及用于填充列的 JSON 属性路径。由于 OPENJSON 返回一组行，因此可以在 Transact-SQL 语句的 FROM 子句中使用 OPENJSON，就如同可以使用任何其他表、视图或表值函数一样