Postgresql -查询jsonb抛出语法错误

Question

我有一个表，有一个jsonb类型的列data。

create table event
(
id             bigserial
primary key,
created_at     timestamp with time zone default now() not null,
type           text                                   not null,
created_by     text,
data           jsonb,
event_time     timestamp with time zone default now() not null
);

在该字段中保存的json对象如下所示:

{
"comment": "Changed by recipient",
"source": "Recipient page"
}

我想通过datajson对象的comment属性的值来查询该表中的值。像这样的东西是基于例子[这里][1]:

select * from event
where type = 'pickup-data-changed' 
and data -> 'comment' = 'Changed by recipient'

如果我像这样查询，我会得到一个无效的令牌错误:

[22P02] ERROR: invalid input syntax for type json Detail: Token "Changed" is invalid. Position: 104

我在这里做错了什么?如果我像评论中建议的那样使用双箭头:

select * from event
where type = 'pickup-data-changed' 
and data ->-> 'comment' = 'Changed by recipient'

我得到一个错误:

[42883] ERROR: operator does not exist: jsonb ->-> unknown Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

我怎样才能使这个查询工作?[1]: https://kb.objectrocket.com/postgresql/how查询- - - postgres jsonb列- 1433

Answer 1

我得到一个无效的令牌错误。我哪里做错了?

data -> 'comment'返回jsonb类型的值，因此比较'Changed by recipient'的右侧也被解析为JSON -并且它是无效的JSON。要创建一个JSON字符串值进行比较，您需要写入

… data -> 'comment' = '"Changed by recipient"'

如果我像评论中建议的那样使用双箭头，data ->-> 'comment'

注释建议

… data ->> 'comment' = 'Changed by recipient'

not->->.

Answer 2

备选项:

select * from event
where type = 'pickup-data-changed'
and data -> 'comment' = '"Changed by recipient"'::jsonb;

或

select * from event
where type = 'pickup-data-changed'
and data['comment'] = '"Changed by recipient"'::jsonb;