How to diagnose and fix the 22037 non_unique_keys_in_a_json_object error code in Postgres.

by

The 22037 error code in PostgreSQL, which refers to “non_unique_keys_in_a_json_object”, indicates that there are duplicate keys within a JSON object where unique keys are expected. This can occur when you’re inserting or updating JSON data in PostgreSQL and the operation expects the JSON keys to be unique, but the input JSON violates this constraint.

To diagnose and fix this issue, you need to identify the JSON data that contains the duplicate keys and then modify the JSON to ensure that each key is unique. Here are multiple examples and sample code to explain how to handle this:

Example 1: Using a SELECT statement to identify duplicate keys

You can use a SELECT statement with the jsonb_object_keys function to get all keys from the JSON object and then use COUNT and GROUP BY to find duplicates.

SELECT key, COUNT(*)
FROM (
  SELECT jsonb_object_keys(json_data) AS key
  FROM your_table
) AS keys
GROUP BY key
HAVING COUNT(*) > 1;

Replace json_data with your actual JSON column and your_table with your actual table name. This query will return keys that appear more than once in the JSON object.

Example 2: Removing duplicate keys from a JSON object

If your application logic allows for removing duplicate keys, you can create a function to return a JSON object with only unique keys, keeping the last occurrence of each key.

CREATE OR REPLACE FUNCTION make_json_keys_unique(json_data JSONB) RETURNS JSONB AS $$
DECLARE
  result JSONB := '{}';
BEGIN
  FOR key, value IN SELECT * FROM jsonb_each(json_data) LOOP
    result := result || jsonb_build_object(key, value);
  END LOOP;
  RETURN result;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

You can then use this function to update your table:

UPDATE your_table
SET json_data = make_json_keys_unique(json_data);

Example 3: Using a constraint to ensure unique keys upon insertion

PostgreSQL 16 introduced the ability to enforce unique keys in a JSON object through a constraint. When you create a table or alter an existing one, you can add a constraint that will throw an error if a JSON object with duplicate keys is inserted.

CREATE TABLE your_table (
  id SERIAL PRIMARY KEY,
  json_data JSONB,
  CONSTRAINT ensure_unique_json_keys CHECK (jsonb_typeof(json_data) = 'object' AND jsonb_each(json_data) IS NOT NULL)
);

-- OR for an existing table

ALTER TABLE your_table
ADD CONSTRAINT ensure_unique_json_keys CHECK (jsonb_typeof(json_data) = 'object' AND jsonb_each(json_data) IS NOT NULL);

This constraint will prevent the insertion of non-unique keys in the JSON objects of the json_data column.

Remember to replace your_table and json_data with your actual table name and JSON column name. When you try to insert or update a JSON object with duplicate keys, PostgreSQL will raise an error, and you will need to correct the JSON object to comply with the unique key constraint.

These examples should cover the possibilities for diagnosing and fixing the 22037 error code related to non-unique keys in a JSON object in PostgreSQL. Keep in mind that the specific solution may depend on your application’s requirements and the structure of your data.

Leave a Comment