使用JSON来实现数据库中的动态条件匹配

在项目的数据库设计中，可能会碰到不确定列的情况。 使用JSON 格式来保存这种不确定列的数据， 是一种比较灵活且方便的做法。 

 

比如一张表记录学生的成绩。

 

 

如果列上的学科是不确定的， 比如有的学生有物理成绩， 有的学生有生物成绩，那么我们可能会考虑将表设计成：

 

 

但这样，可能就会在查询的时候，遇到麻烦。比如我想知道数学成绩是4分的有哪些学生？

类似的问题， 我在项目中使用的做法是写了一个自定义的函数: fun_scale_matchJsonPattern 

/*
 *  Author : Harvey Hu
 *  Created At :2020-6-5
 *  Description : 按指定单个模式判断匹配JSON是否满足（>= 条件即为匹配）
 *  Return =1 匹配 ； =0 不匹配
 */
CREATE FUNCTION [dbo].[fun_scale_matchJsonPattern]
(
     @pattern nvarchar(1000),  -- 单个模式
     @input nvarchar(1000)
 ) returns int
 begin
     Declare @targetScore int = 0; 
     Declare @actualScore int = 0; 
     Declare @result int = 0; 
      
     select @targetScore= count(*) from OPENJSON(@pattern) a

     select @actualScore = count(1)
        from OPENJSON(@pattern) a
           inner join OPENJSON(@input) b on a.[key]= b.[key] and a.[value] = b.[value]

     IF @targetScore = @actualScore
        SET @result = 1
     
     return @result

 end

 

 使用的示例如下

select  
   *
from dbo.[学生成绩表]
where 
   dbo.fun_scale_matchJsonPattern([成绩],'{"数学":"4"}')=1

 

简单的来说，就是比较条件的数量和匹配的条件数量是否一致，如果一致则认为满足。

这种方式的好处是， 可以使用 'and' 逻辑去匹配多个条件；不好的地方在于只能判断‘相等逻辑’，不适用于‘大于’‘小于’等其他的二元逻辑。 不过由于工作上够用了， 所以没有做更多的扩展。如果有需要可以在评论中留言。

 

基于上述内容， 下面这个是‘多个条件取其一即可满足’的实现

 /*
 *  Author : Harvey Hu
 *  Created At :2020-6-5
 *  Description : 按指定多个模式判断匹配JSON是否满足（只要有其中之一满足即为匹配）
 *  Return =1 匹配 ； =0 不匹配
 */
CREATE FUNCTION [dbo].[fun_scale_matchJsonPatterns]
(
    @patterns nvarchar(1000),  -- 数组JSON格式
    @input nvarchar(1000)
 ) returns int  
 as
 begin
    DECLARE @key nvarchar(50)
    DECLARE @value nvarchar(1000)
    DECLARE @type int
    DECLARE @result int =0

   DECLARE jsonCursor CURSOR for 
   select * from OPENJSON(@patterns)

   OPEN  jsonCursor
   fetch next from jsonCursor into @key,@value ,@type

   while @@fetch_status=0           --判断是否成功获取数据   进入循环
   begin
         if @type = 5  and  dbo.[fun_scale_matchJsonPattern](@value,@input)=1  -- object
              set @result = 1
   fetch next from jsonCursor into @key,@value ,@type
   end


   CLOSE jsonCursor

   return @result
 end

 

使用了游标， 所以性能上可能会有影响。 期待改进！