如何检查Postgres中是否存在json密钥?

时间:2021-01-02 23:46:33

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_a and key_b are 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_json and 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_key equating to a boolean? What's the proper syntax to perform this check?

为什么outer_key等于布尔值?执行此检查的正确语法是什么?

2 个解决方案

#1


13  

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 NOT in front of the NULL).

这是完全正常运行,并匹配您的函数名称(注意NULL前面的NOT)。

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)

#2


20  

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 jsonb type.

注意:仅适用于jsonb类型。

#1


13  

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 NOT in front of the NULL).

这是完全正常运行,并匹配您的函数名称(注意NULL前面的NOT)。

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)

#2


20  

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 jsonb type.

注意:仅适用于jsonb类型。