我在 postgresql 中有一个 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

这应该可以，但我收到此错误:

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

最佳答案

您可以使用 elem->'occupation2' 返回 json 类型的字符串 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')::text = 'null'

{"name2": "Zaphod", "occupation2": null}

如果您想获取 JSON 中值为 null 或 key 不存在的所有元素，您可以这样做:

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}

https://stackoverflow.com/questions/19422640/