PostgreSQL JSONB Query: Matching Values of Different Types (String, Integer) Using jsonb_path_exists and like_regex

I have a PostgreSQL database with a JSONB column named properties. I'm trying to create a query that matches all elements with a value inside properties that matches a certain value.

Currently, I'm using a combination of jsonb_path_exists and like_regex, like this:

SELECT id FROM mytable WHERE jsonb_path_exists(properties, '$.data.** ? (@ like_regex "(?i)Foo.*")');

This works well for string values, but the problem arises with non-string elements. For example, if I want to search for an integer value, like_regex returns false every time.

Is there a way to fix this, such as using something like @.toString()?`

If you want to try it yourself

SELECT jsonb_path_exists('{"data": {"foo1": 344, "foo2": "Tata"}}', '$.data.** ? (@ like_regex "(?i)344.*")');

1. Access all values with strict $.data.**
2. Exclude objects and arrays along the way to get only the bottom scalars.
3. Strip the jsonb wrapping from around the value with #>>'{}' (empty path accessor). Casting straight to text would otherwise add quotes.
4. Run your regex.

_{demo at db-fiddle}

create table mytable(id,properties)as values
 (0,'{"data": {"mismatch": "340", "foo8": true}}'::jsonb)
,(1,'{"data": {"foo1": 344, "foo2": "Tata"}}'::jsonb)
,(2,'{"data": {"foo3": 344567, "foo4": "Don''t bring Tata into this"}}'::jsonb)
,(3,'{"data": {"foo5": "344abc", "foo6": "Luke, I am your Tata"}}'::jsonb)
,(4,'{"data": {"foo7": "344", "foo8": true}}'::jsonb);

SELECT id 
FROM mytable
WHERE EXISTS(
  SELECT FROM jsonb_path_query( properties
                               ,'strict $.data.** 
                                         ?(   @.type() != "object" 
                                           && @.type() != "array")')AS a(v)
  WHERE (v#>>'{}') ~ '(?i)344.*' ))
AND properties @? '$.data';

The mismatch from row zero got skipped:

As pointed out by @Bergi, this might be useful for 3 weeks tops, until November 14th 2024 when PostgreSQL 17 will bring more JSON type conversion methods:

Add jsonpath methods to convert JSON values to other JSON data types (Jeevan Chalke)
The jsonpath methods are .bigint(), .boolean(), .date(), .decimal([precision [, scale]]), .integer(), .number(), .string(), .time(), .time_tz(), .timestamp(), and .timestamp_tz().