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

json, postgresql, sql

Solution

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}

Problem

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 ```

Original source