Проверьте, содержит ли многомерный массив Postgres JSON определенное строковое значение
Добрый день,
Я нашел пару решений на SO, относящихся к поиску строк со столбцом 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": {}
}
Как мне получить все строки из таблицы, где свойство replyPreview массива столбцов JSON содержит значение response-text-z?
Я пробовал следующее:
SELECT * FROM dialog_sessions WHERE (session_data->'lastMessages')::jsonb ? 'response-text-z' LIMIT 100
однако безрезультатно.
Ответы 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 с использованием Postgres 11
@RookieSA: тогда вы используете более старую версию Postgres. Смотрите мое редактирование для альтернативы
Спасибо за альтернативу. Я попробовал ваше предложение, однако оно тоже возвращает ошибку, утверждая, что оператор '@>' не существует. Я использую версию 11.17 из-за ее совместимости с Windows Server 2012 R2.
Ну оператора @ действительно не существует. Но я использовал @>
Спасибо. Я принял ваш ответ, так как он предоставил решение. Все, что мне нужно было сделать, это явно привести столбец session_data к типу jsonb, поскольку сам столбец имеет тип json.
@RookieSA: jsonb — рекомендуемый тип данных для хранения значений JSON. Вы должны преобразовать столбец в jsonb навсегда.
Спасибо за быстрый ответ. При попытке запроса я получаю следующую ошибку: ОШИБКА: оператор не существует: json @? неизвестный