Как запросить нулевые значения в поле типа json postgresql?

У меня есть поле типа json в postgresql. Однако я не могу выбрать строки, где определенное поле равно null:

код:

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

Это должно работать, но я получаю эту ошибку:

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

Ответ 1

вы можете использовать тот факт, что elem->'occupation2' возвращает строку null типа json, поэтому ваш запрос будет выглядеть следующим образом:

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}

Если вы хотите получить все элементы, где значение null в JSON или ключ не существует, вы можете просто сделать:

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}

Ответ 2

Если вы ищете нулевое значение в json-blob, вы можете рассмотреть возможность использования функции json_typeof(json), которая была введена в Postgres 9.4:

INSERT INTO table
  VALUES ('{ "value": "some", "object": {"int": 1, "nullValue": null}}');

SELECT * FROM table
  WHERE json_typeof(json->'object'->'nullValue') = 'null';

Это приведет к тому, что вы найдете свою запись для нулевого значения.

Надеюсь, это поможет!

Ссылка: http://www.postgresql.org/docs/9.4/static/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE

Ответ 3

Ответы @roman-pekar и @mraxus были полезны, но я был неудовлетворен без возможности четко различать undefined и null... так что я придумал:

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

Он короткий и также дает мне сигнал, если значение json равно undefined (возвращает null). Может быть, это полезно для этого вопроса.