Question

I have a SQL column that contains array data such as:

["Jimmy","Johnny","Jeffry","Jacob","Justin","Joseph","Josh","Jared"] 
or could be 
["Jimmy","Johnny","Jeffry"].

我需要寻找与栏目中所有名字相对应的姓名。查询可以有1个或全部名字。

I am currently using OPENJSON to filter on individual names like Jimmy or Johnny but now the requirement is if they choose All I need to bring back the columns that only contain ALL the names, not just 1 name so -

["Jimmy","Johnny","Jeffry","Jacob","Justin","Joseph","Josh","Jared"]
 and not 
["Jimmy","Johnny"]

因此,当他们选择统一概念一中的所有名字时,他们就想把即将到来的斜线作为所有名字(上文所列所有名字)的列表。

 @Names =  Jimmy,Johnny,Jeffry,Jacob,Justin,Joseph,Josh,Jared

我目前正在研究一个单独项目:

 (@Names IN ( SELECT value FROM OPENJSON(a.Names)) OR @Names IS NULL)

我认为,这一要求可能不属于开放Json的范围,但不能确定什么是试图尝试的,或许可能是独立的独立实体。

Any suggestions or questions would be appreciated.

Answer 1

并非只是规定在<代码>@上没有名字的条件。姓名/代码 <代码>中不存在的阵列页: 1

/*Sample Data*/
DECLARE @T TABLE
  (
     Names NVARCHAR(MAX)
  );

INSERT @T
VALUES (N ["Jimmy","Johnny","Jeffry","Jacob","Justin","Joseph","Josh","Jared"] ),
      ( N ["Jimmy","Johnny","Kevin"] )


/*Example @Names value to simulate your parameter*/
DECLARE @Names NVARCHAR(MAX) = N ["Jimmy","Johnny","Jeffry"] ;

/*Just parse it once, remove any dupes and use PK so SQL Server can see this*/
DECLARE @NamesToFind TABLE(Name NVARCHAR(100) PRIMARY KEY);

INSERT @NamesToFind (Name)
SELECT DISTINCT value
FROM   OPENJSON(@Names)

/*The actual SELECT*/
SELECT *
FROM   @T T
WHERE  NOT EXISTS (SELECT Name
                   FROM   @NamesToFind
                   EXCEPT
                   SELECT value
                   FROM   OPENJSON(T.Names))

If @Names is null this will match anything as looks desired from the OR @Names IS NULL in your current code. And if you want to just match a single name then pass in an array with one element.

This will not be able to do any kind of index seek and will require a full scan of some kind.

友情链接