检查Postgres JSON多维数组是否包含特定的字符串值

Question

大家好，

我在S.O上找到了一些解决方案，用于查找具有指定值的JSON列的行。

我目前面临的问题是，我的特定JSON列(session_data)包含一个多维数组，具有一个或多个值:

{
"lastMessages": [
{
"eventId": "1",
"replyDate": "2022-11-23T05:47:18.577Z",
"replyPreview": "response-text-a"
},
{
"eventId": "2",
"replyDate": "2022-11-23T05:48:14.550Z",
"replyPreview": "response-text-b"
},
{
"eventId": "3",
"replyDate": "2022-11-23T06:23:53.234Z",
"replyPreview": "response-text-c"
},
{
"eventId": "4",
"replyDate": "2022-11-23T06:24:13.555Z",
"replyPreview": "response-text-d"
},
{
"eventId": "5",
"replyDate": "2022-11-23T06:24:30.919Z",
"replyPreview": "response-text-z"
}
],
"workflows": {},
"slots": {}
}

我该如何从JSON列数组的replyPreview属性包含值response-text-z的表中检索所有行?

我试过以下方法:

SELECT * FROM dialog_sessions WHERE (session_data->'lastMessages')::jsonb ? 'response-text-z' LIMIT 100

但是无济于事。

Answer 1

您可以使用JSON路径表达式:

select *
from dialog_sessions 
where session_data @? '$.lastMessages[*].replyReview == "response-text-z"'

如果您使用的是较旧的Postgres版本，您可以尝试@>操作符:

select *
from dialog_sessions 
where session_data -> 'lastMessages' @> '[{"replyPreview": "response-text-z"}]'

DbFiddle using Postgres 11