时间戳

首页 > 解决方案 > 在 JSON 列中查找 Array 的长度

sql - 在 JSON 列中查找 Array 的长度

问题描述

我正在尝试对我的查询结果打分。每个'categories'数组可能包含许多类别整数，也可能是null. 如果它为空，则应将其视为已找到。

在下面的查询中，我正在搜索找到的类别3。但是我不知道如何检查 Array 类别是否是 NULL这样我也可以返回 1 这将解决我的问题。

我努力了：

WHERE Value IN(3) OR Value IS NULL)

WHERE Value IN(3) OR Value.length =0)

WHERE Value IN(3) OR Value = '')

ETC....

例如

  1. {"categories":[1,2,3],"distance":[130,300],"companysize":[2,4],"budget":[5000,1000000]}
  2. {"categories":[],"distance":[60,200],"companysize":[1,2],"budget":[250,100]}

SQL

     SELECT * FROM (SELECT 
             (CASE WHEN EXISTS (Select * FROM OPENJSON(Preference,'$.categories') 
              WHERE Value IN(3) OR Value IS NULL)  -- < problem is here
              THEN 1 ELSE 0 END)
              matchscore, p.*
              FROM FinNotificationsPreferences p) x

标签： sqlsql-serversql-server-2016

解决方案

尝试替换您的代码：

Select * FROM OPENJSON(Preference,'$.categories') WHERE Value IN(3) OR Value IS NULL

具有以下内容：

SELECT * FROM OPENJSON (Preference) WHERE [key] = 'categories' AND ([value] like '%3%' OR [value] = '[]')

您使用 OPENJSON(Preference, '$.categories) 的问题在于，当数组为空时，OPENJSON 表函数不返回任何行。这与类别数组具有值但它们都不是“3”的情况没有区别。所以，我们需要改变我们的方法。

通过简单地检查 OPENJSON(Preference) 并解析 3 或 '[]' 的类别值，我们可以返回 1 行或 0 行。1 行表示找到我们的条件，0 表示未命中。

注意：这种解析有一些很大的缺点。如果有像 13、23、30、300 等这样的类别......所有这些都将匹配我们的 %3% 模式。我留给你根据需要加强解析。我不知道你的用例。

推荐阅读