J'essaie d'utiliser une recherche partielle dans ma clause WHERE à partir d'un filtre JSON . Je passe une chaîne délimitée par , et je souhaite séparer celle utilisée dans l'instruction where.
Par exemple:
Un exemple de table:
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), ',')) + '%')
Voici le filtre qui est passé:
DECLARE @Filter NVARCHAR(MAX)
SET @Filter=N'{
"SomeObject": "ello,yel"
}'
Et j'essaye de tout sélectionner dans la table qui contient '% ello % ' ou '%yel%'
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')
Cette requête fonctionne partiellement. J'ai de nombreuses instances dans lesquelles la sous-requête renverra plus d'une ligne. Je peux passer à la clause IN , mais je ne pourrais pas effectuer de recherche partielle. Des suggestions sur la façon de résoudre ce problème?
EDIT:
Sur la base du filtre ci-dessus, la sortie doit renvoyer toutes les lignes contenant ello ou yel dans la colonne SomeObject . Donc, dans cet exemple, devrait renvoyer les lignes 1 et 2.
3 Réponses :
Une méthode serait d'utiliser OPENJSON pour analyser le JSON, puis STRING_SPLIT pour diviser la liste délimitée:
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;
Comme , cependant, les lignes peuvent être mises en correspondance plusieurs fois ( 'Yellow' contient à la fois la chaîne 'yel' et 'ello' ), vous avez également besoin un CTE pour garantir qu'une seule ligne par Id est renvoyée.
+1 pour me donner la bonne sortie. Cependant, malheureusement, je ne suis pas en mesure de l'implémenter facilement dans ma requête plus large. La sortie utilise essentiellement ce filtre pour filtrer les données, puis renvoyer le résultat en tant qu'objet json. J'ai pensé à diviser json et à le mettre dans la table cte et à joindre le cte mon résultat final, mais cela pose toujours des problèmes.
On dirait que nous n'avons reçu qu'un petit morceau d'une image beaucoup plus grande. Je suggère de poster le vrai problème dans votre question ici @ smr5 si vous voulez une réponse qui résout votre problème dans son intégralité. Quelles sont les informations dont nous disposons, cette réponse y répond.
tu as raison. Je ne m'attendais pas à ce que ce soit si compliqué. Tout ce que je voulais savoir, c'est Comment construire une instruction OR dans ma clause WHERE à partir d'une chaîne séparée par , , car la sous-requête s'étouffe sur plus d'un résultat. Je suppose qu'il n'y a pas de moyen simple.
Il ne renvoie aucun résultat.
essayez de déboguer la sortie dans la sélection elle-même voir quelle est la sortie avec remplacer avez-vous supprimé la requête de sélection avant de remplacer
Vous obtenez cela à partir du REPLACE elloyel% pour toutes les lignes.
où est le % de départ devrait être comme % elloyel%
C'est la sortie que j'obtiens de cette sélection. sélectionnez REPLACE (JSON_VALUE (@ Filter, N '$. SomeObject'), ',', '') + '%' à partir de la nouvelle table
peut essayer de remplacer , par % ainsi parce que maintenant, ce qui se passe est la chaîne complète concatée elloyel devrait être là dans bonjour jaune etc, ce qui n'a pas été le cas jusqu'à présent, car la sortie est de 0 enregistrements. utilisez sélectionnez REPLACE (JSON_VALUE (@ Filter, N '$. SomeObject'), ',', '%') . j'espère qu'après cela, cela fonctionne parce que maintenant, la sortie sera `% ello% yel%` ce qui signifie que bonjour n jaune doit correspondre
utilisez ce format d'expression régulière LIKE '% [' + select REPLACE (JSON_VALUE (@ Filter, N '$. SomeObject'), ',', ']% [') + ']%'
+1 pour vos efforts pour m'aider. Vous êtes proche, mais toujours pas correct. Tout d'abord, il vous manque ( avant SELECT et ) à la fin. Mais le problème ici est que si vous ne fournissez qu'une seule valeur dans le filtre comme "SomeObject": "test" , il renvoie toutes les lignes. Et si aucun filtre n'est spécifié, la première condition doit tout renvoyer, mais ce n'est pas le cas.
Si je vous comprends bien, vous pouvez essayer ceci:
-- 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], '%')
)
)
votre réponse fonctionne presque. J'ai à l'origine cette condition pm.SomeObject IS NULL AND JSON_VALUE (@ Filter, N '$. SomeObject') IS NULL OR ... dans mon code pour tout renvoyer si le filtre n'est pas spécifié. Mais dans votre code, si le filtre est vide, il ne retournera rien. J'ai essayé de modifier le code à l'intérieur du WHERE EXISTS en (pm.SomeObject IS NULL AND JSON_VALUE (@Filter, N '$. SomeObject') IS NULL OU pm.SomeObject LIKE CONCAT (' % ', [Value],'% ')) mais il ne renvoie toujours rien si aucun filtre n'est spécifié. Une suggestion sur la façon de tout retourner si aucun filtre n'est spécifié? PAS EXISTANT ?
@ smr5 J'ai mis à jour la réponse avec quelques clauses where supplémentaires. Voyez si cela aide. Merci.
Merci beaucoup! Pouvez-vous modifier votre réponse et ajouter cette instruction OU (JSON_VALUE (@Filter, '$ .SomeObject') IS NULL) OU dans le second cas? Au cas où si j'avais le filtre défini comme SET @ Filter = N '{} , il ne renverrait pas toutes les lignes et il n'obtiendrait simplement rien. Ce qui précède prendra soin de ce scénario.
@ smr5 La réponse est modifiée. Avec cette condition OR , j'ai ajouté un autre AND dans le 3ème cas. Vérifiez si cela fonctionne comme prévu. Merci.
si vous pouvez partager l'échantillon d'entrée et la sortie souhaitée, cela le rendrait un peu plus clair.
@HimanshuAhuja, j'ai ajouté plus de description.
@HimanshuAhuja, que voulez-vous dire?
quelle est la sortie de courant que vous obtenez
Cela génère une erreur car la sous-requête
renvoie plus d'une ligne.