How do I check if a json key exists in Postgres?

In PostgreSQL databases, checking for the existence of specific JSON keys can be achieved through various methods, depending on your requirements and the structure of the JSON data. Below, I will introduce common approaches to verify the presence of specific keys within JSON.

Method 1: Using the ? Operator with jsonb Data Type

If your column is of the jsonb type, you can use the ? operator to check if a key exists. This operator returns a boolean value indicating whether the key is present.

Example: Assume there is a data column of type jsonb, and you want to check if the key 'user_id' exists. You can use the following SQL query:

sql
SELECT id, data
FROM your_table
WHERE data ? 'user_id';

This query returns all rows where the data column contains the 'user_id' key.

Method 2: Using the -> Operator with json Data Type

If your column is of the json type, you can use the -> operator to retrieve the value of the key and then check if it is null.

Example: Assume there is an info column of type json, and you want to check if the key 'username' exists. You can use the following SQL query:

sql
SELECT id, info
FROM your_table
WHERE info->'username' IS NOT NULL;

This query returns all rows where the info column contains the 'username' key and the corresponding value is not null.

Method 3: Using the jsonb_typeof() Function

This method applies to jsonb types and uses the jsonb_typeof() function to retrieve the type of the key, then checks if this type is not null.

Example: Assume settings is a jsonb column, and you want to verify if the key 'theme' exists:

sql
SELECT id, settings
FROM your_table
WHERE jsonb_typeof(settings->'theme') IS NOT NULL;

This query checks whether the type of the 'theme' key in the settings column is not null, thereby confirming the key's existence.

Method 4: Using EXISTS with json_each* Functions

For checking multiple keys or performing complex verifications, combine the json_each or json_each_text (for json types) and jsonb_each or jsonb_each_text (for jsonb types) functions with the EXISTS statement.

Example: Assume attributes is a jsonb column, and you want to check if the keys 'height' and 'width' exist:

sql
SELECT id
FROM your_table
WHERE EXISTS (
    SELECT 1
    FROM jsonb_each_text(attributes) as kv(key, value)
    WHERE key IN ('height', 'width')
);

This query expands each key-value pair in the attributes column and checks for the presence of the keys 'height' or 'width'.

By employing these methods, you can select the most suitable approach based on your specific requirements and JSON data types to verify the existence of specific keys within JSON.