使用JSON來實現資料庫中的動態條件匹配

在項目的資料庫設計中，可能會碰到不確定列的情況。 使用JSON 格式來保存這種不確定列的數據， 是一種比較靈活且方便的做法。 

 

比如一張表記錄學生的成績。

 

 

如果列上的學科是不確定的， 比如有的學生有物理成績， 有的學生有生物成績，那麼我們可能會考慮將表設計成：

 

 

但這樣，可能就會在查詢的時候，遇到麻煩。比如我想知道數學成績是4分的有哪些學生？

類似的問題， 我在項目中使用的做法是寫了一個自定義的函數: fun_scale_matchJsonPattern 





 1 /*
 2  *  Author : Harvey Hu
 3  *  Created At :2020-6-5
 4  *  Description : 按指定單個模式判斷匹配JSON是否滿足（>= 條件即為匹配）
 5  *  Return =1 匹配 ； =0 不匹配
 6  */
 7 CREATE FUNCTION [dbo].[fun_scale_matchJsonPattern]
 8 (
 9      @pattern nvarchar(1000),  -- 單個模式
10      @input nvarchar(1000)
11  ) returns int
12  begin
13      Declare @targetScore int = 0; 
14      Declare @actualScore int = 0; 
15      Declare @result int = 0; 
16       
17      select @targetScore= count(*) from OPENJSON(@pattern) a
18 
19      select @actualScore = count(1)
20         from OPENJSON(@pattern) a
21            inner join OPENJSON(@input) b on a.[key]= b.[key] and a.[value] = b.[value]
22 
23      IF @targetScore = @actualScore
24         SET @result = 1
25      
26      return @result
27 
28  end




 


 使用的示例如下




1 select  
2    *
3 from dbo.[學生成績表]
4 where 
5    dbo.fun_scale_matchJsonPattern([成績],'{"數學":"4"}')=1




 


簡單的來說，就是比較條件的數量和匹配的條件數量是否一致，如果一致則認為滿足。


這種方式的好處是， 可以使用 ‘and’ 邏輯去匹配多個條件；不好的地方在於只能判斷『相等邏輯』，不適用於『大於』『小於』等其他的二元邏輯。 不過由於工作上夠用了， 所以沒有做更多的擴展。如果有需要可以在評論中留言。


 


基於上述內容， 下面這個是『多個條件取其一即可滿足』的實現




 1  /*
 2  *  Author : Harvey Hu
 3  *  Created At :2020-6-5
 4  *  Description : 按指定多個模式判斷匹配JSON是否滿足（只要有其中之一滿足即為匹配）
 5  *  Return =1 匹配 ； =0 不匹配
 6  */
 7 CREATE FUNCTION [dbo].[fun_scale_matchJsonPatterns]
 8 (
 9     @patterns nvarchar(1000),  -- 數組JSON格式
10     @input nvarchar(1000)
11  ) returns int  
12  as
13  begin
14     DECLARE @key nvarchar(50)
15     DECLARE @value nvarchar(1000)
16     DECLARE @type int
17     DECLARE @result int =0
18 
19    DECLARE jsonCursor CURSOR for 
20    select * from OPENJSON(@patterns)
21 
22    OPEN  jsonCursor
23    fetch next from jsonCursor into @key,@value ,@type
24 
25    while @@fetch_status=0           --判斷是否成功獲取數據   進入循環
26    begin
27          if @type = 5  and  dbo.[fun_scale_matchJsonPattern](@value,@input)=1  -- object
28               set @result = 1
29    fetch next from jsonCursor into @key,@value ,@type
30    end
31 
32 
33    CLOSE jsonCursor
34 
35    return @result
36  end




 


使用了游標， 所以性能上可能會有影響。 期待改進！

		  				

		  				

													


						
							

							
																
Tags:      

																

								

							

						
						
						


						 

							
					

					    
									

													

													
											


			


			


	

		
VirMach 便宜 VPS
 


QNews
 
QNews