如何在json字段类型postgresql中查询空值? [英] How to query for null values in json field type postgresql?

问题描述

我在postgresql中有一个json类型字段.但是我无法选择特定字段为null的行:

I have a json type field in postgresql. However I can't select rows where specific field is 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

这应该可以，但是出现了这个错误:

This should work but I am getting this error:

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

推荐答案

您可以使用以下事实:elem->'occupation2'返回类型为json的字符串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}

如果要获取JSON中值为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}

这篇关于如何在json字段类型postgresql中查询空值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！