Querying PostgreSQL JSONB column with varying types for data

Question

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.

@LaurenzAlbe server is currently running PostgreSQL 11. But I'd be willing to update to 12 or 13 if it enables this. Will update post to reflect — Norrec, Feb 12 '21 at 07:30

score 2 · Accepted Answer · 2021-02-12T11:08:05.477

2

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

edited Feb 12 '21 at 11:08

answered Feb 12 '21 at 07:37

Ah this is perfect, I will have to look into updating our server to Postgres 12 then. Thank you! – Norrec Feb 12 '21 at 07:40
1

@Norrec: if you upgrade, then go directly to the latest version (13). No need to go for 12 – Feb 12 '21 at 07:42
1

If you plan to use this approach in an application that takes external data, sending the SQL via any language-specific driver (psycopg2, Npgsql and so on) take extreme care in building the expression because you can't use a bound variable in that position and unless you do your own quoting you're susceptible of SQL injection. – fog Feb 12 '21 at 08:37
@fog: there is a way to pass this is a parameter through e.g. PreparedStatement - but it's not really pretty ;) – Feb 12 '21 at 08:49
@a_horse_with_no_name really? Can you give me some pointers? The ability to bind a variable inside a text would be really helpful is some cases. – fog Feb 12 '21 at 10:56

score 2 · Answer 2 · answered Feb 12 '21 at 07:50

I don't remember exactly in which version was introduced but you can use the json_typeof function and a CASE expression to convert the value of the property to the correct type. I would use an SQL function, to keep my queries all tidy and clean:

CREATE FUNCTION jsonb_to_integer(jsonb, text) RETURNS integer AS
$$
    SELECT CASE jsonb_typeof($1->$2) 
           WHEN 'number' THEN ($1->>$2)::integer 
           ELSE null 
           END 
$$
LANGUAGE SQL
STABLE
RETURNS NULL ON NULL INPUT;

And then just:

SELECT * FROM TABLE WHERE jsonb_to_integer(data, 'rootProperty') > 1;

Querying PostgreSQL JSONB column with varying types for data

2 Answers2