¿Cómo consultar valores nulos en json field type postgresql?

Tengo un campo tipo json en postgresql. Sin embargo, no puedo seleccionar filas donde el campo específico es nulo:

Código:

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 

Esto debería funcionar, pero estoy obteniendo este error:

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

puede usar el hecho de que elem->'occupation2' devuelve cadena null de tipo json , por lo que su consulta será:

 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} 

Si desea get todos los elementos donde value es null en JSON o la key no existe, puede hacer lo siguiente:

 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} 

Si está buscando un valor nulo dentro de json-blob, json_typeof(json) considerar usar la function json_typeof(json) que se introdujo en Postgres 9.4:

 INSERT INTO table VALUES ('{ "value": "some", "object": {"int": 1, "nullValue": null}}'); SELECT * FROM table WHERE json_typeof(json->'object'->'nullValue') = 'null'; 

Esto dará como resultado que encuentre su input para el valor nulo.

¡Espero que esto ayude!

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

Las respuestas de @ roman-pekar y @mraxus fueron útiles pero no estaba satisfecho sin la capacidad de distinguir claramente indefinido y nulo … así que, se me ocurrió:

 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 

Es corto y también me da una señal si el valor json no está definido (devuelve nulo). Quizás es útil para esta pregunta.