Search code examples
postgresqljsonbquerying

Querying PostgreSQL JSONB column with varying types for data

As part of my schema in my PostgreSQL DB, currently running version 11, but willing to upgrade if it unblocks: I have a jsonb column data, which contains nested objects of various structure across the rows, which I don't have control over. Ex:

row 1 might be: {'rootProperty': { 'nestedProperty': 'someStrVal' }}

and row 2 might have a similar schema: {'rootProperty': { 'nestedProperty': 2, 'otherNestedProperty': 'someOtherString' }}

My difficulty comes in when trying to query for a row/subset of rows based on a property in the jsonb column that has different types across the rows. In this example, nestedProperty is a string in row 1 and an int in row 2.

When I try to run a query such as

SELECT * FROM TABLE WHERE data -> 'rootProperty' ->> 'nestedProperty' = 'someStrVal'

Things run fine, but if I try

SELECT * FROM TABLE WHERE data -> 'rootProperty' ->> 'nestedProperty' > 1

or

SELECT * FROM TABLE WHERE (data -> 'rootProperty' ->> 'nestedProperty')::int > 1

The queries error out, with a 'operator does not exist: text > integer' and 'invalid input syntax for integer: "someStrVal"' respectively.

Is there a way I can have jsonb column with variable schemas that may have overlapping structures albeit with different data types and still query for them all? I don't mind having to specify the type I'm looking for, so long as it can just skip or bypass the rows that don't meet that type criteria.

Solution

  • Having different types of values for the same property seems rather strange to begin with, but most probably you can't change that "design".

    Postgres 12 introduced support for SQL/JSON path expression which are lenient to data type conversions and don't error out if you try to compare someStrVal with a number.

    The query:

    select *
from the_table
where data @@ '$.rootProperty.nestedProperty > 1'

    Will return all rows where the nestedProperty is a valid number an greater than 1. Values that can't be converted to a number are silently ignored.

    This can also be written as:

    where jsonb_path_exists(the_column, '$.rootProperty.nestedProperty ? (@ > 1)')

    The value can be passed as a parameter by using jsonb_path_exists() with a third argument:

    where jsonb_path_exists(the_column, '$.rootProperty.nestedProperty ? (@ > $nr)', '{"nr": 1}')

    he last argument can be passed through a parameter placeholder, e.g. in Java:

    where jsonb_path_exists(the_column, '$.rootProperty.nestedProperty ? (@ > $nr)', cast(? as jsonb))

    And then:

    PreparedStatement pstmt = conn.prepareStatement(...);
pstmt.setString(1, "{\"nr\": 1}");