Python 如何检查 Postgres 中是否存在 json 键？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28921355/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I check if a json key exists in Postgres?
提问by Teboto
Let's say I have a json that looks like this:
假设我有一个如下所示的 json：
some_json = {'key_a': {'nested_key': 'a'},
'key_b': {'nested_key': 'b'}}
Note that key_aand key_bare optional keys mapped to dictionaries and may or may not exist.
请注意，key_a和key_b是映射到字典的可选键，可能存在也可能不存在。
I have a function that checks if an outer key exists in some_jsonand returns a boolean.
我有一个函数来检查是否存在外部键some_json并返回一个布尔值。
CREATE FUNCTION key_exists(some_json json, outer_key text)
RETURNS boolean AS $$
BEGIN
RETURN (some_json->outer_key IS NULL);
END;
$$ LANGUAGE plpgsql;
I get the following error:
我收到以下错误：
ProgrammingError: operator does not exist: json -> boolean
Why is outer_keyequating to a boolean? What's the proper syntax to perform this check?
为什么outer_key等于布尔值？执行此检查的正确语法是什么？
采纳答案by X-Istence
Your function does the exact opposite of what the name is, but the way to fix your function is to add (and )around the some_json->outer_key.
你的函数执行的名称是完全相反的，但解决您的函数的方法是添加(和)周围some_json->outer_key。
Here is it fully functioning, and matching the name of your function (notice the NOTin front of the NULL).
这是它功能齐全，并与您的函数名称匹配（注意NOT前面的NULL）。
CREATE FUNCTION key_exists(some_json json, outer_key text)
RETURNS boolean AS $$
BEGIN
RETURN (some_json->outer_key) IS NOT NULL;
END;
$$ LANGUAGE plpgsql;
Some tests:
一些测试：
select key_exists('{"key_a": {"nested_key": "a"}, "key_b": {"nested_key": "b"}}'::json, 'key_a');
key_exists
------------
t
(1 row)
And here when a key doesn't exist:
当密钥不存在时：
select key_exists('{"key_a": {"nested_key": "a"}, "key_b": {"nested_key": "b"}}'::json, 'test');
key_exists
------------
f
(1 row)
回答by DaL
You can also use the '?' operator like that:
您也可以使用“？” 像这样的运算符：
SELECT '{"key_a":1}'::jsonb ? 'key_a'
And if you need to query by nested key, use like this:
如果您需要按嵌套键查询，请像这样使用：
SELECT '{"key_a": {"nested_key": "a"}}'::jsonb -> 'key_a' ? 'nested_key'
See http://www.postgresql.org/docs/9.5/static/functions-json.html
见http://www.postgresql.org/docs/9.5/static/functions-json.html
NOTE: Only for jsonbtype.
注意：仅适用于jsonb类型。
回答by Piyush Sharma
To check key exists or not you can use the operator -> this is used to get Get JSON object field by key For example:
要检查密钥是否存在，您可以使用运算符 -> 这用于通过密钥获取 Get JSON 对象字段例如：
actual json data in column(attribute): {
"active": "t",
"email_address": "[email protected]",
"pin": "2233"
}
SELECT attributes::json->'email_address'
FROM entity
WHERE entity_id = 55;
You can also search key via operator #> and #>>
您还可以通过运算符 #> 和 #>> 搜索关键字
Get JSON object field as text: '{"a":1,"b":2}'::json->>'b' by using the operator ->>
获取 JSON 对象字段作为文本：'{"a":1,"b":2}'::json->>'b' 使用运算符 ->>
