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
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}
elem->'occupation2'::text does not work but (as you have), (elem->'occupation2')::text does work.
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
For a specific JSON value, cast expression to jsonb and compare it to 'null'::jsonb.
Here, the objective is to find an object that has the key occupation2 and value JSON null. The comparison must not confuse the existence of the key with the value.
Adapting the original:
SELECT *
FROM json_array_elements(
'[{"name": "Toby", "occupation": "Software Engineer"},
{"name": "Zaphod", "occupation": "Galactic President"},
{"name2": "Zaphod", "occupation2": null}]'
) AS elem
WHERE (elem#>'{occupation2}')::jsonb = 'null'::jsonb
;
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'
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
