PostgreSQL Question posted in
The official documentation can be found here.

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.*")');
Tags:

3

Answers


  1. 0

    I think you could use this query for your purpose

    SELECT id FROM mytable WHERE properties->'data'->>'some_key' ~ '(?i)Foo.*';

    To break it down for you, -> operator navigates through the JSON and ->> extracts the value as text.

    Login or Signup to reply.
  2. 0
    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:

    id
    1
    2
    3
    4

    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().

    Login or Signup to reply.
  3. 0

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

    Postgres 17 (currently in development) will (prospectively) come with a @.string() method in JSON path that does exactly this: "String value converted from a JSON boolean, number, string, or datetime".

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search