Check if Postgres JSON multidimensional array contains specific string value-CodePudding

Good day,

I've found a couple solutions on S.O pertaining to finding rows with its JSON column having a specified value.

The issue I'm currently facing, is that my specific JSON column (session_data) contains a multidimensional array, with one or several values:

{
    "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": {}
}

How would I go about retrieving all rows from a table where the JSON column array's replyPreview property contains the value response-text-z?

I've tried the following:

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

however to no avail.

CodePudding user response：

You can use a JSON path expression:

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

If you are on an older Postgres version you can try the @> operator:

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

DbFiddle using Postgres 11