iT邦幫忙

0

在SQLServer計算欄位解析JSON並將之加總

Declare @ColumnA as varchar(20)
Set @ColumnA='[12,21,113]'
SELECT sum(Convert(int,[value])) FROM OPENJSON(@ColumnA)

1.想要的結果:計算[12,21,113] = 146. (但是要寫在MSSQL計算欄位)

2.試過:有試過先寫UDF再拿來套。 但SYS似乎不允許有Select語法在其中。

想請教邦友們..有沒有什麼能在[計算欄位]解析JSON並且將之加總的寫法? thanks.


目前使用下列方式 會報錯. 但是卻可以正常運作.

ALTER function [dbo].[SumJsonString] (@jsonString nvarchar(500))
returns int
as
begin

declare @resultStr int

Set @resultStr = isnull(Convert(int,JSON_VALUE(@jsonString, '$[0]')),0)

  • isnull(Convert(int,JSON_VALUE(@jsonString, '$[1]')),0)
  • isnull(Convert(int,JSON_VALUE(@jsonString, '$[2]')),0)
  • isnull(Convert(int,JSON_VALUE(@jsonString, '$[3]')),0)
  • isnull(Convert(int,JSON_VALUE(@jsonString, '$[4]')),0)

return @resultstr

end

我安裝2017~測試SQL是可以呢
我有搞錯你的問題點嗎??
還是你要2016版以下~可以解析JSON資料??
cnct2004 iT邦新手 5 級 ‧ 2018-12-22 15:21:11 檢舉
我要表達的是當上述的語法要下在 【公式】計算欄位中時.則會報錯.
感謝先進回應.

1 個回答

0
純真的人
iT邦高手 1 級 ‧ 2018-12-20 11:25:40

根據MSSQL文件~應該是可以的.
https://docs.microsoft.com/zh-tw/sql/t-sql/functions/openjson-transact-sql?view=sql-server-2017

只是SQL寫法...線上資料庫掛點無法測試
http://sqlfiddle.com

手邊也沒有MSSQL2016版以上的可以測試@@..


特地在win10電腦裝MSSQL2007
這方式可以呢..

Declare @ColumnA as varchar(20)
Set @ColumnA='[12,21,113]'
declare @tmp table(
	num int
)

insert into @tmp
select [value]
from OPENJSON(@ColumnA)

select Sum(num) as num
from @tmp

或者..我剛剛試也是可以@@..

Declare @ColumnA as varchar(20)
Set @ColumnA='[12,21,113]'

select Sum(Convert(int,[value])) as Num
from OPENJSON(@ColumnA) as k

https://ithelp.ithome.com.tw/upload/images/20181221/20061369mXp4ZLoYat.png


20181224測試ok~
1.先建立函數~

create function dbo.JsonSumNum(@Str nvarchar(max))
returns int
as 
begin
	declare @Num int;
	select @Num = Sum(Convert(int,[value]))
	from OPENJSON(@Str)
	return @Num;
end
Go
select Test_Json
,dbo.JsonSumNum(Test_Json) as Test_SumNum
from Test

https://ithelp.ithome.com.tw/upload/images/20181224/20061369cqBrVk0z3q.png

2.然後就可以將Json字串丟入計算~

select *
,dbo.JsonSumNum(Test_Str) as JsonSumNum
from Test

https://ithelp.ithome.com.tw/upload/images/20181224/20061369ENc9f4NL7m.png

看更多先前的回應...收起先前的回應...
cnct2004 iT邦新手 5 級 ‧ 2018-12-22 15:12:43 檢舉

是的. 我早先貼出的方式是在查詢語句上可以執行.
但是當它被貼入SQLSERVER 的計算欄位中時,則不可行.(報錯)

cnct2004 iT邦新手 5 級 ‧ 2018-12-22 15:15:20 檢舉

https://ithelp.ithome.com.tw/upload/images/20181222/20113285jW6DdMAYC6.png

cnct2004 iT邦新手 5 級 ‧ 2018-12-22 15:16:12 檢舉

[dbo].[SumJsonString] (ColumnName)

喔喔~你是說丟在資料表欄位的公式~
直接解析計算Json的資料呀...
例如Test資料表的欄位是Test_Json字串~
但Test_SumNum是Json解析後加總的欄位~
對吧?
例如:

Test_ID Test_Json    Test_SumNum
1       [12,21,113]  146
cnct2004 iT邦新手 5 級 ‧ 2018-12-24 09:26:56 檢舉

是的!

那function應該這樣~
因為公式是直接計算的~
用function比較好
以下是這樣的想法~
要回去測試2017是否知道有無錯誤

create function dbo.JsonSumNum(@Str nvarchar(max))
returns int
as 
begin
	declare @Num int;
	select @Num = Sum(Convert(int,[value]))
	from OPENJSON(@Str)
	return @Num;
end
Go
select Test_Json
,dbo.JsonSumNum(Test_Json) as Test_SumNum
from Test

恩恩~~測試ok~你再試試~

cnct2004 iT邦新手 5 級 ‧ 2018-12-25 17:04:12 檢舉

因為某些原因不希望寫在SQL字串中,故才有此一問.
所有方法的前提會是要在 Select * from TableName 中出現
而非select dbo.jsonSumNum(text_json) from tableName

https://ithelp.ithome.com.tw/upload/images/20181225/20113285i0LOZ3VWok.png

早先有說明已測試可用,但卻會報錯. 以上感謝回覆.

我要發表回答

立即登入回答