How to query for null values in json field type postgresql?

Question

I have a json type field in postgresql. However I can't select rows where specific field is null:

Code:

SELECT *
FROM   json_array_elements(
  '[{"name": "Toby", "occupation": "Software Engineer"},
    {"name": "Zaphod", "occupation": "Galactic President"} ,
{"name2": "Zaphod", "occupation2": null} ]'  ) AS elem
where elem#>'{occupation2}' is null

This should work but I am getting this error:

ERROR:  operator does not exist: json #> boolean
LINE 6: where elem#>'{occupation2}' is null

score 82 · Answer 1 · answered Oct 17 '13 at 12:35

82

you can use the fact that elem->'occupation2' returns string null of type json, so your query will be:

select
    *
from  json_array_elements(
  '[{"name": "Toby", "occupation": "Software Engineer"},
    {"name": "Zaphod", "occupation": "Galactic President"} ,
    {"name2": "Zaphod", "occupation2": null} ]'
) as elem
where (elem->'occupation2')::text = 'null'

{"name2": "Zaphod", "occupation2": null}

If you want to get all elements where value is null in JSON or key doesn't exists, you can just do:

select
    *
from  json_array_elements(
  '[{"name": "Toby", "occupation": "Software Engineer"},
    {"name": "Zaphod", "occupation": "Galactic President"} ,
    {"name2": "Zaphod", "occupation2": null} ]'
) as elem
where (elem->>'occupation2') is null

{"name": "Toby", "occupation": "Software Engineer"}
{"name": "Zaphod", "occupation": "Galactic President"}
{"name2": "Zaphod", "occupation2": null}

answered Oct 17 '13 at 12:35

Roman Pekar

107,110
28
195
197

2

This worked for me in plpgsql as well. I had to place the json extract in brackets before checking if it is null. – Imraan Apr 03 '14 at 13:13
2

Maybe the associativity rules were obvious to others (and maybe this is what @Imraan was saying as well), but I didn't realize the importance of parentheses before casting to text. Meaning, `elem->'occupation2'::text` does not work but (as you have), `(elem->'occupation2')::text` does work. – dwanderson Oct 12 '16 at 20:42
The second example worked for me. I was missing the parentheses. – Cristian Apr 13 '18 at 06:35
Hi @roman, Could you share any postgres documentation stating this behaviour? – ndsurendra Jan 20 '19 at 06:16

mraxus · Answer 2 · 2017-06-13T11:25:55.990

If you are searching for a null value within a json-blob you might want to consider using the function json_typeof(json) that was introduced in Postgres 9.4:

INSERT INTO table
  VALUES ('{ "value": "some", "object": {"int": 1, "nullValue": null}}');

SELECT * FROM table
  WHERE json_typeof(json->'object'->'nullValue') = 'null';

This will result in you finding your entry for the null value.

Hope this helps!

Reference: http://www.postgresql.org/docs/9.4/static/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE

Reinsbrain · Answer 3 · 2020-08-24T14:50:12.237

The answers from @roman-pekar and @mraxus were helpful but I was unsatisfied without the ability to clearly distinguish undefined and null... so, I came up with:

CREATE OR REPLACE FUNCTION isnull (element json)
RETURNS boolean AS $$
  SELECT (element IS NOT NULL) AND (element::text = 'null');
$$ LANGUAGE SQL IMMUTABLE STRICT;

select isnull('{"test":null}'::json->'test'); -- returns t
select isnull('{"test":"notnull"}'::json->'test'); -- returns f
select isnull('{"toot":"testundefined"}'::json->'test'); -- returns null

@a_horse_with_no_name also pointed out the additional jsonb operator ? introduced in postgresql version 9.4:

SELECT '{"a":1, "b":2}'::jsonb ? 'b'

To distinguish between null and a non-existing key, you can use the `?` operator to test if the key is present. — , Aug 21 '20 at 12:05

score 2 · Answer 4 · edited Aug 21 '20 at 12:56

2

Use dbeaver editor for this, it is working there.

SELECT * FROM json_array_elements('[{"name": "Toby", "occupation": "Software Engineer"},{"name": "Zaphod", "occupation": "Galactic President"},{"name2":"Zaphod","occupation2":null}]') AS elem
where elem#>'{occupation2}') IS NULL

edited Aug 21 '20 at 12:56

Fletcher Rippon

1,837
16
21

answered Aug 21 '20 at 11:40

Asim Ali

19
1

score 0 · Answer 5 · answered Sep 01 '23 at 18:30

For a specific JSON value, cast expression to jsonb and compare it to 'null'::jsonb.

Here, the objective is to find an object that has the key occupation2 and value JSON null. The comparison must not confuse the existence of the key with the value.

Adapting the original:

SELECT *
  FROM json_array_elements(
      '[{"name": "Toby", "occupation": "Software Engineer"},
        {"name": "Zaphod", "occupation": "Galactic President"},
        {"name2": "Zaphod", "occupation2": null}]'
      ) AS elem
    WHERE (elem#>'{occupation2}')::jsonb = 'null'::jsonb
;

How to query for null values in json field type postgresql?

5 Answers5

Linked