1
votes

Filtrer les lignes en fonction des valeurs dans plusieurs colonnes JSONB

J'essaye de rechercher une table dans un LIKE %str% mode mais sur des champs à l'intérieur des valeurs json sur plusieurs colonnes .

J'ai une table qui a trois colonnes jsonb change , previous et specific_changes . Comme vous pouvez l'imaginer, le contenu est JSON mais la structure de ce json n'est pas connue à l'avance, je ne peux donc pas utiliser le -> ou ->> dans la requête comme ceci:

select * from change_log where change -> 'field' = '"something"'

  INPUT           OUTPUT(ids)
  "2"              (1,2)
  "Change"         (2,3)
  "Chan"           (2,3)
  "Value"         (1,2,3)

Ma question est:

Comment une requête ressemblerait-elle à celle donnée une chaîne retournera toutes les lignes de la table change_log dont l'une des 3 colonnes jsonb mentionnées contient des champs qui ont une valeur like %string% .
comment rendre la requête insensible à la casse

Exemples:

create table change_log
(
    id               serial      not null
        constraint pk_change_log
            primary key,
    change           jsonb       not null,
    previous         jsonb,
    changed_at       timestamp with time zone default timezone('utc'::text, now()),
    specific_changes jsonb
);

INSERT INTO public.change_log (id, change, previous, changed_at, specific_changes) VALUES (1, '{"val": 2, "test": "test", "nested": {"nval": 1}}', 'null', '2020-11-12 16:53:28.827896', '{"val2": "Value2"}');
INSERT INTO public.change_log (id, change, previous, changed_at, specific_changes) VALUES (2, '{"val": "testNewChange", "test": "testChange", "nested": {"key": 1}}', '{"val": "2", "test": "testChange", "nested": {"nval": 1}}', '2020-11-15 12:18:35.021843', '{"new": "testValue"}');
INSERT INTO public.change_log (id, change, previous, changed_at, specific_changes) VALUES (3, '{"val": "newewChange", "test": "changeNew", "nested": {"val": 3}}', '{"val": "testNewChange", "test": "testValue", "nested": {"key": 1}}', '2020-11-15 12:19:40.832843', '{"new": "testChange", "nested": {"val": 1}}');

EDIT1: J'utilise la version 9.6 de postgres

EDIT2: Correction des modifications insérées pour refléter le comportement souhaité

postgresql jsonb postgresql-9.6

2 commentaires

Pourquoi l' id=1 est omis pour Change alors qu'il contient la clé "test": "testChange" ? Voulez-vous également vérifier les objets imbriqués? Si oui, pourquoi id=3 n'est pas inclus pour la valeur 2 alors qu'il contient "nested": {"val": 2} ?

@Abelisto vous avez attrapé mon erreur, j'ai mis à jour les valeurs insérées pour qu'elles correspondent à l'entrée et à la sortie spécifiées

3 Réponses :

1
votes

Vous pouvez interroger comme

SELECT DISTINCT l.id
  FROM change_log l
 CROSS JOIN JSONB_EACH_TEXT( l.change ) AS c(e)
 CROSS JOIN JSONB_EACH_TEXT(  nullif(l.previous, 'null') ) AS p(e) 
 CROSS JOIN JSONB_EACH_TEXT( l.specific_changes ) AS s(e)
 WHERE c.value ~* 'change' OR s.value ~* 'change' OR p.value ~* 'change'

où l'opérateur ~* recherche une correspondance insensible à la casse du mot-clé donné et la fonction JSONB_EACH_TEXT() développe l'objet JSON le plus externe en un ensemble de paires clé / valeur.

PS Besoin de corriger la valeur 'null' en la convertissant en null pour la valeur id 1 de la colonne previous ou d'utiliser nullif(l.previous, 'null') comme argument pour le deuxième JSONB_EACH_TEXT () dans la requête

Démo

4 commentaires

nullif(value, 'null') Cependant, something cross join empty retournera empty (supprimez simplement l' where de votre requête pour vous assurer) ...

Comme je n'ai pas mentionné que [22023] ERROR: cannot call jsonb_each_text on a non-object postgres 9.6, la requête fournie donne [22023] ERROR: cannot call jsonb_each_text on a non-object , à peu près sûr que c'est lié à la version

non @vuk, ce problème n'est pas lié à la version mais au littéral 'null' dans la colonne previous . Soit le convertir en null comme je l'ai mentionné précédemment, soit appliquer la fonction nullif() comme indiqué dans le commentaire ci-dessus et ma modification est effectuée tout à l'heure. Btw, la démo en 9.6 maintenant.

Merci beaucoup et j'apprécie la patience requise en raison de mes faibles compétences en SQL!

3
votes

Si vous utilisez Postgres 12 ou version ultérieure, vous pouvez utiliser une expression de chemin SQL / JSON:

select *
from change_log
where change @@ '$.** like_regex "change" flag "i"'
   or previous @@ '$.** like_regex "change" flag "i"'
   or specific_changes @@ '$.** like_regex "change" flag "i"'

0 commentaires

1
votes

L'approche courante pour les anciennes versions de PostgreSQL utilise exists avec une fonction, comme

select *
from table_name
where
    exists (
        select 1
        from (
            select * from jsonb_each_text(column1) union all
            select * from jsonb_each_text(column2)) as t(k,v)
        where t.v ilike '%string%');

Pour plusieurs colonnes, cela peut être fait en utilisant or :

select *
from table_name
where
    exists (
        select 1
        from jsonb_each_text(column1) as t(k,v)
        where v ilike '%string%') or
    exists (
        select 1
        from jsonb_each_text(column2) as t(k,v)
        where v ilike '%string%');

ou union :

select *
from table_name
where exists (
    select 1
    from jsonb_each_text(column_name) as t(k,v)
    where v ilike '%string%');

Démo

Notez qu'il ne traitera pas correctement les objets imbriqués car ils seront vérifiés comme un texte entier, clés comprises.

Pour résoudre ce problème, vous devez créer la fonction stockée qui renvoie toutes les valeurs de JSON de manière récursive.

Mais c'est le sujet d'une autre question :)

0 commentaires