59

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
Alexandru R
  • 8,024
  • 16
  • 59
  • 93

4 Answers4

73

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}
Roman Pekar
  • 99,839
  • 26
  • 181
  • 193
  • 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
13

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

mraxus
  • 1,207
  • 1
  • 15
  • 22
1

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'
Reinsbrain
  • 1,805
  • 2
  • 20
  • 32
1

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
Fletcher Rippon
  • 1,461
  • 14
  • 19
Asim Ali
  • 9
  • 1