postgresql undefined json field does not return null as expected

Question

The goal is to return all records where a certain key is missing from a JSON data type. Just to make it obvious, I'm trying to query the JSON structure for anything where faultyField returns null among the following table data:

 id::SERIAL |       data::JSON
------------+-------------------
          1 | {"key" : "val"}
          2 | {"key1" : "val2"}

Any of the following two statements:

SELECT * FROM test WHERE data->>'faultyField' = null;
SELECT * FROM test WHERE (data->>'faultyField')::text = 'null';

Should return all values (according to two SO posts I found at work)? But it doesn't for some reason. These two just return empty results.

I'm no wizard with PostgreSQL so I've tried everything in between these statements below just to see if they work at all. Sadly they doesn't.

SELECT * FROM test WHERE (SELECT json_object_keys(data) FROM test)='key';
SELECT * FROM test WHERE 'key' in json_object_keys(data);
SELECT * FROM test WHERE 'key' := json_object_keys(data);
SELECT * FROM test WHERE 'key' ANY (json_object_keys(data));

Is there a way to return records where a key is missing from the JSON string?

Answer 1

Do not use = with null. Try:

select *
from test
where data->>'faultyField' is null;

From the documentation:

Do not write expression = NULL because NULL is not "equal to" NULL. (The null value represents an unknown value, and it is not known whether two unknown values are equal.)

Answer 2

EDIT

I heavily recommend you to use JSONB instead of JSON. It is a more powerful type that allows indexing and has additional operators such I demonstrated below.

---

If you want to test if a key don't exists in the top-level of jsonb field use ? operator:

CREATE TEMP TABLE test AS VALUES 
    (1, '{"key" : "val"}'::JSONB),
    (2, '{"key2" : "val2"}'::JSONB);

SELECT * FROM test WHERE NOT column2 ? 'key3';

Using data->>'faultyField' will attempt to get the value of this key rather than test if the key exists. This isn't what you're looking for because it will return rows even faultyField key exists and has a null value.