Search code examples
postgresqlpostgres-14

Postgres jsonb_path_exists 'like' functionality for numbers

I currently am trying to implement a search based off one of my postgres tables. The type of the column I am searching is jsonb and I am using jsonb_path_exists like the following

select project
from search_table
where jsonb_path_exists(project, '$.** ? (@ like_regex "${searchString}" flag "i")');

Where ${searchString} is a variable being passed in. This is working great when the json is a string type and I get all the results I would expect. However I cannot figure out how to get this to work if the json field is a number type.

For example if I pass in an ID where in the json it is defined as number, something like id:123456

I would like to be able to pass in 123 and have the object with a value like 123456 being picked up. Basically a 'like' but against a number type. If the id was id:'123456' it works fine, but since it is a number the current implementation won't pick it up. Is there a way to use jsonb_path_exists where I can pick up both string and number types with a 'like' type expression?

My current workaround is casting the whole thing to text and searching like that

select project 
from search_table
where UPPER(project::text) like UPPER('%${searchString}%')

However, this just seems like an unnecesary step and I would prefer to just search the json values with jsonb_path_exists. I am using postgres 14.7, any help would be appreciated, thanks.

***UPDATE with min example

CREATE TABLE IF NOT EXISTS dmspgdev.zz_yache_test
(
    project_id numeric(19,0),
    project jsonb
)

insert into dmspgdev.zz_yache_test
values (1,'{"id":123456, "data":"test1"}');

insert into dmspgdev.zz_yache_test
values (2,'{"id":789323, "data":"search works with string"}');

-- works because data is string
select project
from dmspgdev.zz_yache_test
where jsonb_path_exists(project, '$.** ? (@ like_regex "search" flag "i")');

-- No results because id is number
select project
from dmspgdev.zz_yache_test
where jsonb_path_exists(project, '$.** ? (@ like_regex "456" flag "i")');
Solution

  • You could do it like this:

    select project 
from dmspgdev.zz_yache_test 
where project->>'id' ~~ '%${searchString}%' 
or project ->>'data' ~~* '%${searchString}%';

    using the operator ilike "~~*" case insensitive LIKE and dividing maters for each of your json fields. Without the need of casting types since the operator "->>" gets the json object field as text.