1

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

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

My question is:

  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%.

  2. how would you make the query case insensitive

Examples:

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

EDIT1: I am using postgres version 9.6

EDIT2: Fixed inserted changes to reflect desired behavior

Improve this question
2
  • Why the id=1 is omitted for Change while it contains "test": "testChange" key? Do you want to check the nested objects as well? If yes, why id=3 is not included for 2 value while it contains "nested": {"val": 2}? Commented Nov 15, 2020 at 19:20
  • @Abelisto you caught my mistake, i updated the inserted values to match the specified input and output Commented Nov 15, 2020 at 22:15

3 Answers 3

Reset to default
3

If you are using Postgres 12 or later, you can use a SQL/JSON path expression:

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"'
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

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

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

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

Demo

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

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 :)

Improve this answer

1 Comment

Thanks, I had a similar problem and this solution really helped me. Just for my understanding, what is the name of the operator you used as an alias for the set returned by jsonb_each_text? I tried (k,v) on its own but the query failed; it only worked when I used t like t(k, v).
1

You can query like

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'

where ~* operator searches for case-insensitive matching of the given keyword and the function JSONB_EACH_TEXT() expands the outermost JSON object into a set of key/value pairs.

P.S. Need to fix the value 'null' by converting to null for the id 1 value of the previous column or use nullif(l.previous, 'null') as the argument for the second JSONB_EACH_TEXT() within the query

Demo

Improve this answer

4 Comments

nullif(value, 'null') However something cross join empty will return empty (just remove where in your query to ensure) ...
As i failed to mention that i am using postgres 9.6 the provided query is giving [22023] ERROR: cannot call jsonb_each_text on a non-object, pretty sure that is version related
no @vuk , this problem is not related to the version but the literal 'null' within the previous column. Either convert it to null as I previously mentioned, or apply nullif() function as stated in the above comment and my edit performed just now. Btw, the demo in 9.6 now.
Thank you very much and i appreciate the patience requiered due to my poor sql skills!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.