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 

Answer 1

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} 

Answer 2

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