Postgresql jsonb check if key exists

by

To check if a key exists in a JSONB column in PostgreSQL, you can use the JSONB operators and functions provided by PostgreSQL. Below is an example of how you can perform this check.

Let’s say you have a table called “employees” with a JSONB column named “data”, which stores employee information in JSON format. And you want to check if a specific key “department” exists in the “data” column for a certain employee. 


    CREATE TABLE employees (
      id serial PRIMARY KEY,
      data jsonb
    );

To check if the “department” key exists for an employee with a certain id, you can use the “->” operator to access the value of the key and then the “??” operator to check if it exists. 


    SELECT id
    FROM employees
    WHERE data->'department' ?? 'engineering';

In the above example, we are checking if the “department” key exists with the value “engineering” in the “data” column. If the key exists, it will return the id of the employee.

Additionally, you can use the “jsonb_exists” function for a similar check. It returns true if the key exists and false otherwise. 


    SELECT id
    FROM employees
    WHERE jsonb_exists(data, 'department');

In the above example, we are checking if the “department” key exists in the “data” column using the “jsonb_exists” function. If the key exists, it will return the id of the employee.

Leave a comment