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?

klin · Accepted Answer · 2017-06-21 17:26:12Z

7

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.)

edited Jun 21, 2017 at 17:26

answered Jun 21, 2017 at 17:18

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Michel Milezzi Over a year ago

I guess the op is trying to check if a key exists rather than test its value. In this case data->>'faultyField' is null will return rows even faultyField key exists and has a null value. But I can be wrong.

klin Over a year ago

Well, you're wrong. data->>'faultyField' is null is true if and only if the key is missing.

Michel Milezzi Over a year ago

Are you sure?

CREATE TEMP TABLE test AS SELECT 1 AS id, '{"key" : null}'::JSONB AS data; SELECT * FROM test WHERE data->>'key' IS NULL;

. This test case returns the row as I expected. Tested on 9.5.6.

Torxed Over a year ago

Works perfectly from what I can tell :)

Michel Milezzi · Accepted Answer · 2017-06-22 00:56:53Z

7

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.

edited Jun 22, 2017 at 0:56

answered Jun 21, 2017 at 17:28

Michel Milezzi

11.4k3 gold badges24 silver badges36 bronze badges

6 Comments

Torxed Over a year ago

No operator matches the given name and argument type(s). You might need to add explicit type casts. on the ? - why is this?

Michel Milezzi Over a year ago

The ? operator exists for JSONB type since 9.4. What's your PostgreSQL version? More info on docs.

Torxed Over a year ago

I'm rocking v9.6.1 currently. JSONB is for bytes data tho?

Michel Milezzi Over a year ago

No. JSONB stores json data in decomposed binary format. There are several advantages in comparison with simple JSON type, which is stored as a text, e.g: support for indexing. In general you might prefer to use JSONB instead of JSON.

Michel Milezzi Over a year ago

Well, to avoid operator error you should cast your column to JSONB. Something like: SELECT * FROM test WHERE NOT data::JSONB ? 'key3';

|

Collectives™ on Stack Overflow

postgresql undefined json field does not return null as expected

2 Answers 2

4 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related