根据多个JSONB列中的值过滤行 [英] Filter rows based on values inside multiple JSONB columns

问题描述

我正在尝试以LIKE %str%的方式搜索表，但在多列的json值内的字段上.

I am trying to search a table in a LIKE %str% fashion but on fields inside json values over multiple columns.

我有一个表，其中包含三个jsonb列change，previous和specific_changes.您可能会想像内容是JSON，但事先不知道JSON的结构，因此我不能在查询中使用->或->>，如下所示:

I have a table which has three jsonb columns change,previous and specific_changes. As you might imagine the content is JSON but the structure of that json is not know ahead of time, therefor i can't use the -> or ->> in query like so:

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

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}}');

我的问题是:

1. 给定 string 的查询将如何返回change_log表中的所有行，其中提到的3个jsonb列中的任何一个包含任何值为like %string%的字段.

1. How would a query look like that given a string will return all rows from the change_log table whose any of the 3 mentioned jsonb columns contain any fields that has a value like %string%.

如何使查询不区分大小写

how would you make the query case insensitive

示例:

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

我使用的是Postgres 9.6版

I am using postgres version 9.6

修复了插入的更改以反映所需的行为

Fixed inserted changes to reflect desired behavior

推荐答案

旧版本PostgreSQL的常用方法是将exists与某些功能一起使用，例如

The common approach for the old versions of the PostgreSQL is using exists with some function, like

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

对于几列，可以使用or完成:

For several columns it could be done using 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%');

或union:

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%');

演示

请注意，它将无法正确处理嵌套对象，因为它们将作为一个完整的文本(包括键)进行检查.

Note that it will not process properly the nested objects because them will be checked as a whole text, including keys.

要解决此问题，您需要创建一个存储函数，该存储函数以递归方式从JSON返回所有值.

To fix this you need to create the stored function that returns all values from JSON recursively.

但这是另一个问题的主题:)

But it is the subject for another question :)

这篇关于根据多个JSONB列中的值过滤行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！