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

How to handle invalid cast operations in JSONB column queries in PostgreSQL?

I have a column called tags of type JSONB in my PostgreSQL table called EMPLOYEE.

There is a use case where I want to allow users to query this JSONB column, i.e., tags.

Users can give inputs like:

  • tags.inoffice.present = 5
  • tags.inoffice = 5

These inputs are then converted to the following queries in my python application:

SELECT * 
FROM EMPLOYEE 
WHERE CAST((((EMPLOYEE.tags -> 'inoffice')) ->> 'present') AS FLOAT) = 5.0;

and

SELECT * 
FROM EMPLOYEE 
WHERE CAST((((EMPLOYEE.tags)) ->> 'inoffice') AS FLOAT) = 5.0;

The first query returns the correct output as expected. However, the second query fails because it cannot cast into a float, resulting in the following error:

psql:commands.sql:29: ERROR:  invalid input syntax for type double precision: "{"present": 5}"

The tags column is a free field, and the table can contain any JSON structure. Therefore, there are no predefined valid/invalid key-value pairs in that column.

What is the best way to prevent this problem?

Solutions that I have considered:

  • Using a try except block in the Python application to catch errors. If an error is raised, check if the given path can be cast to float and if not, raise a ValueError. However, I am unsure how to check if the given input path can be converted to float or not.

Is there any other better way to handle this situation?

Here is a sample playground for the above-mentioned issue.


-- create
CREATE TABLE EMPLOYEE (
  empId INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  tags JSONB
);


-- insert
INSERT INTO EMPLOYEE (empId, name, tags)
VALUES 
(1, 'John Doe', '{"inoffice": {"present": 5}}'),

(2, 'Jane Smith', '{"inoffice": {"present": 15}}'),

(3, 'Bob Johnson', '{"inoffice": {"present": 20}}'),

(4, 'Alice Brown', '{"inoffice": {"present": 4}}');


-- fetch 
SELECT * 
FROM EMPLOYEE 
WHERE CAST((((EMPLOYEE.tags -> 'inoffice')) ->> 'present') AS FLOAT) < 12.0;

SELECT * 
FROM EMPLOYEE 
WHERE CAST((((EMPLOYEE.tags)) ->> 'inoffice') AS FLOAT) < 12.0;
Tags:

2

Answers


  1. 0

    The @? and @@ operators take a path query and silently ignore errors. (The former is for SQL-standard JSON path queries, the latter for Postgres-specific boolean predicates.)

    SELECT e.*
FROM EMPLOYEE e
WHERE e.tags @? '$.inoffice.present.double() ? (@ < 12.0)';

SELECT e.* 
FROM EMPLOYEE e
WHERE e.tags @? '$.inoffice.double() ? (@ < 12.0)';

    You can also do this with jsonb_path_exists if you pass the silent parameter as true. You can also use injected parameters using this method with the vars parameter.

    SELECT e.*
FROM EMPLOYEE e
WHERE jsonb_path_exists(e.tags, '$.inoffice.present.double() ? (@ < 12.0)', silent => true);

SELECT e.* 
FROM EMPLOYEE e
WHERE jsonb_path_exists(e.tags, '$.inoffice.double() ? (@ < 12.0)', silent => true);

    See also the docs.

    Login or Signup to reply.
  2. 0 
    | However, I am unsure how to check if the given input path 
| can be converted to float or not.

    If I got it right, you want to be able to check if a specific path could be converted to float or not. It is possible using Case expression + regex to do the test and then you could decide what to do.
    Bellow is such a test for paths from your queries using regex to check if there are only numbers in text from start to end….

    1. working path
    SELECT empid,  
       Case When  (((tags -> 'inoffice') ->> 'present')::text)~E'^\d+$'
            Then CAST( ((tags -> 'inoffice') ->> 'present') AS FLOAT)
       Else -1.0
       End as test
FROM EMPLOYEE 

    /*  R e s u l t :
empid   test
-----   ----
    1   5
    2   15
    3   20
    4   4         */
    1. error path
    SELECT empid,  
       Case When  ((tags -> 'inoffice')::text)~E'^\d+$'
            Then CAST( ((tags -> 'inoffice')) AS FLOAT)
       Else -1.0
       End as test
FROM EMPLOYEE 

    /*  R e s u l t :
empid   test
-----   ----
    1   -1
    2   -1
    3   -1
    4   -1        */
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search