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

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 elemwhere (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 elemwhere (elem->>'occupation2') is null{"name": "Toby", "occupation": "Software Engineer"}{"name": "Zaphod", "occupation": "Galactic President"}{"name2": "Zaphod", "occupation2": null}

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

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 elemwhere elem#>'{occupation2}') IS NULL