Check if Postgres JSON multidimensional array contains specific string value

Question

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.

score 1 · Accepted Answer · 2022-11-24 10:06:31Z

1

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

edited Nov 24, 2022 at 10:06

answered Nov 24, 2022 at 9:34

user330315

Sign up to request clarification or add additional context in comments.

6 Comments

RookieSA Over a year ago

Thanks for the prompt response. Upon trying the query, I get the following error: ERROR: operator does not exist: json @? unknown

user330315 Over a year ago

@RookieSA: then you are using and older Postgres version. See my edit for an alternative

RookieSA Over a year ago

Thanks for the alternative. I've tried your suggestion, however this too seems to return an error, stating that the operator '@>' does not exist. I'm using v11.17 for its compatibility with Windows Server 2012 R2.

user330315 Over a year ago

Well, the operator @ does not exist indeed. But I used @>

RookieSA Over a year ago

Thank you. I've accepted your answer, as it has provided the solution. All I've had to do was explicitly typecast the session_data column to jsonb, as the column itself is of json type.

|

Collectives™ on Stack Overflow

Check if Postgres JSON multidimensional array contains specific string value

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related