代码之家 › 专栏 › 技术社区 › smr5

使用like和json对象进行部分搜索

sql-server-2017 sql-server sql

smr5 · 技术社区 · 6 年前

我正在尝试在我的 WHERE A子句 JSON 过滤器。我路过一个 , 分隔字符串,并希望分隔在WHERE语句中使用的字符串。

例如:

样本表:

create table NewTable
(
   Id int identity(1,1) primary key,
   SomeObject varchar(20),
   SomeText varchar(20)
)

INSERT INTO NewTable(SomeObject, SomeText)values ('hello', 'test1')
INSERT INTO NewTable(SomeObject, SomeText)values ('yellow', 'test2')
INSERT INTO NewTable(SomeObject, SomeText)values ('test1', 'test1')

下面是要通过的过滤器:

DECLARE @Filter NVARCHAR(MAX)

SET @Filter=N'{
  "SomeObject": "ello,yel"
}'

我想从桌子上挑选 '%ello%' 或 '%yel%'

select * from newtable pm
where 
(pm.SomeObject IS NULL AND JSON_VALUE(@Filter,N'$.SomeObject') IS NULL OR pm.SomeObject LIKE '%' + (select value from string_split(ISNULL(JSON_VALUE(@Filter,N'$.SomeObject'),pm.SomeObject), ',')) + '%')

此查询部分工作。我有很多例子 subquery 将返回多行。我可以换用 IN 但我不能执行部分搜索。关于如何解决这个问题有什么建议吗?

编辑:

基于上述过滤器,输出应返回 ello 或 yel 在 SomeObject 列。所以在这个例子中,应该返回第1行和第2行。

3 回复 | 直到 6 年前

Zhorov 6 年前

如果我正确理解你,你可以尝试这样做:

-- Table
CREATE TABLE NewTable
(
   Id int identity(1,1) primary key,
   SomeObject varchar(20),
   SomeText varchar(20)
)
INSERT INTO NewTable(SomeObject, SomeText) VALUES ('hello', 'test1')
INSERT INTO NewTable(SomeObject, SomeText) VALUES ('yellow', 'test2')
INSERT INTO NewTable(SomeObject, SomeText) VALUES ('test1', 'test1')

-- JSON
DECLARE @Filter nvarchar(MAX)
SET @Filter = N'{
  "SomeObject": "ello,yel"
}'

-- Statement
SELECT pm.*
FROM NewTable pm
WHERE 
   -- 1. @Filter is null
   (ISJSON(@Filter) IS NULL) OR
   -- 2. $.SomeObject is empty text, @Filter = N'{"SomeObject": ""}'
   (JSON_VALUE(@Filter, '$.SomeObject') = N'') OR
   (JSON_VALUE(@Filter, '$.SomeObject') IS NULL) OR
   -- 3. Filter exists
   (
      (ISJSON(@Filter) IS NOT NULL) AND
      (JSON_VALUE(@Filter, '$.SomeObject') <> N'') AND
      (JSON_VALUE(@Filter, '$.SomeObject') IS NOT NULL) AND
      EXISTS (
         SELECT * 
         FROM STRING_SPLIT(JSON_VALUE(@Filter, '$.SomeObject'), ',') 
         WHERE pm.SomeObject LIKE CONCAT('%', [Value], '%')
      )
   )

Larnu 6 年前

一种方法是 OPENJSON 解析JSON,然后 STRING_SPLIT 要拆分分隔列表:

DECLARE @Filter nvarchar(MAX);

SET @Filter=N'{
  "SomeObject": "ello,yel"
}';
WITH CTE AS(
    SELECT NT.Id,
           NT.SomeObject,
           NT.SomeText,
           ROW_NUMBER() OVER (PARTITION BY NT.ID ORDER BY SS.[Value]) AS RN
    FROM dbo.NewTable NT
        CROSS APPLY OPENJSON(@Filter) OJ
        CROSS APPLY STRING_SPLIT(OJ.[value], ',') SS
    WHERE NT.SomeObject LIKE '%' + SS.[value] + '%')
SELECT Id,
       SomeObject,
       SomeText
FROM CTE
WHERE RN = 1;

但是,由于行可以匹配多次( 'Yellow' 包含两个字符串 'yel' 和 'ello' )您还需要一个CTE来确保每个 Id 返回。

Himanshu 6 年前

也许这将匹配每个字符串 hello 在串联的正则表达式中分离 %[hello]%[yellow]%

  select * from newtable pm
    where 
   (pm.SomeObject IS NULL AND 
  JSON_VALUE(@Filter,N'$.SomeObject') IS NULL 
    OR pm.SomeObject  LIKE '%[' + select 
  REPLACE
    (JSON_VALUE(@Filter,N'$.SomeObject')
            ,',',']%['  ) + ']%' 
     )