2016-03-10

在 SQL 上使用 JSON

微軟已經公佈 SQL Server 2016 將支援原生 JSON 的功能:

JSON Data (SQL Server)
https://msdn.microsoft.com/en-us/library/dn921897.aspx

Format Query Results as JSON with FOR JSON (SQL Server)
https://msdn.microsoft.com/en-us/library/dn921882.aspx

在工作的資料庫沒這麼新,怎麼辦呢?
拜讀 Phil Factor 的文章,可以在既有的資料庫上應用 JSON,而且已經更新到 Update 8 (2014/05),相當穩定。

Consuming JSON Strings in SQL Server
https://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/

原文有詳細的說明,這理只記錄我的使用:

資料表函式:
select * from dbo.json_Parse('{"Amount":5072.1,"DueDate":42398,"ChkID":"PBB400533"}');
select * from dbo.Factor_parseJSON('{"Amount":5072.1,"DueDate":42398,"ChkID":"PBB400533"}');

純量值函式:
SELECT T2.JsonInfo
     , dbo.json_value(T2.JsonInfo, 'ChkID') AS ChkID
     , CONVERT(DATETIME, CONVERT(Decimal, dbo.json_value(T2.JsonInfo, 'DueDate'))) AS DueDate
     , CONVERT(Money, dbo.json_value(T2.JsonInfo, 'Amount')) AS Amount
  FROM [acc].[PayablesM] AS T1
     , [acc].[PayablesD] AS T2
 WHERE T1.CpnID = 'CCP'
   AND T1.SysID = '20160310001'
   AND T2.UID = T1.UID
   AND ISNULL(T2.JsonInfo, '') <> ''


針對 SQL Server 2008 的修改 json_Value
IF  EXISTS (SELECT * FROM sys.objects 
             WHERE object_id = OBJECT_ID(N'dbo.json_Value')
               AND type in ('TF', 'FN', 'IF') ) -- TF = TABLE_FUNCTION ; FN = FUNCTION ; IF = INLINE_TABLE_FUNCTION
DROP FUNCTION [dbo].[json_Value]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/****** json_Value  ******/
CREATE FUNCTION [dbo].[json_Value](@json nvarchar(max),@path nvarchar(max))
RETURNS nvarchar(MAX) 
AS
BEGIN
    declare @data pJsonData
    declare @p1   TABLE(id int identity(1,1),name nvarchar(max))
    
    insert into @data       select id,parent,name,kind,value from dbo.json_Parse(@json)
    set @path=ltrim(rtrim(@path))
    if @path!='' insert into @p1(name)   select * from dbo.ufn_Split(@path,'.')
    
    declare @c0     int =0
    declare @cur    int =null
    declare @step   int =1
    declare @max    int =1
    declare @v      nvarchar(max)
    declare @v2     nvarchar(max)
    declare @kind   nvarchar(max)
    select  @max=max(id) from @p1

    SELECT @c0=id, @cur=id, @kind=kind, @v=value
      FROM @data
      WHERE parent=0 -- Current object is the one with parent = 0

    IF @cur is null RETURN null                                             -- Should not happen
    WHILE (@step<=@max and @c0 is not null) 
    BEGIN
      SELECT @v=name FROM @p1 WHERE id=@step
      SET @c0 =null
      if (IsNumeric(@v)=1) 
      BEGIN
        if (@kind!='ARRAY') return cast('Using index in non array JSON' as int);
        SET @v2=@v

        -- SQL Server 2008
        SELECT @c0=ID, @cur=ID, @kind=kind, @v=value 
          FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID) RID
                     , ID, kind, value
                  FROM @data 
                 WHERE parent = @cur 
               ) AS T1
          WHERE RID = CONVERT(INT, @v)

        -- SQL Server 2012
        --SELECT @c0=ID, @cur=ID, @kind=kind, @v=value 
        --  FROM @data 
        -- WHERE parent = @cur 
        -- ORDER BY ID 
        --       OFFSET convert(int, @v) ROWS 
        --       FETCH FIRST 1 ROW ONLY
      END
      else 
      BEGIN
        if (@kind!='OBJECT') return cast('Using property name in a non-object JSON' as int);
        SELECT @c0=ID,@cur=ID,@kind=kind,@v=value 
          FROM @data
         WHERE parent=@cur and name=@v
      END
      set @step = @step+1
    END
    if @c0 is null return null
    if (@kind='OBJECT' or @kind='ARRAY') return dbo.json_toJson(@data,@cur)
    return @v
END
GO
[JSON & SQL]

沒有留言: