Как фильтровать данные в API PostgreSQL на основе нескольких языков?
Я создаю API, в котором мне нужно фильтровать данные на основе языка. При фильтрации с использованием запроса типа { "language": ["Hindi", "English"] } я хочу получить строки, соответствующие обоим языкам, но только в формате "language": ["Hindi", "English"] .
Однако мои данные содержат вариации. Например:
В одну строку: «Язык»: [«Английский», «Хинди», «Панджаби»] В другом ряду: «Язык»: [«Английский», «Хинди»] Я хочу отправить обе строки в нужном формате: «язык»: [«Английский», «Хинди»]. Важно отметить, что я хотел бы добиться этого с помощью запроса, а не изменения файла контроллера.
Data: {
"id": 169,
"status": 1,
"from_time": "10:30:00",
"to_time": "19:30:00",
"created_at": "2023-11-03 18:05:16",
"updated_at": "2024-03-31 18:11:43",
"language": ["English", "Hindi", "Punjabi"]
},
{
"id": 817,
"status": 1,
"from_time": "10:00:00",
"to_time": "17:30:00",
"created_at": "2024-03-29 17:31:26",
"updated_at": null,
"language": ["English", "Hindi"]
}
Попытка запроса:
SELECT *
FROM tbl_lang
WHERE language ->> 0 = 'Hindi' AND language ->> 1 = 'English'
Я хочу отправить обе строки, но только как «язык»: [«Английский», «Хинди»]. Я не хочу отправлять данные типа этого «языка»: [«Английский», «Хинди», «Пенджаби»] или другие данные типа этого «языка»: [«Английский», «Хинди»].
Это возможно? Если да, может ли кто-нибудь предоставить запрос PostgreSQL для достижения этой цели?
Ответы 1
Если в вашем столбце используется тип jsonb, вы можете использовать оператор ?& (PostgreSQL 9.4+):
jsonb ?& text[] → boolean
Все ли строки в текстовом массиве существуют как ключи верхнего уровня или элементы массива?
Таким образом, вы можете передать один массив/слово языков, которые хотите сопоставить.
Демо на db<>fiddle:
select jsonb_pretty(data) from tbl_lang
where (data->'language') ?& array['English', 'Hindi'];
Если вы работаете с обычным json, а не jsonb, вы можете добавить приведение:
select jsonb_pretty(data) from tbl_lang
where (data->'language')::jsonb ?& array['English', 'Hindi'];
"id": 169,
"статус": 1,
"to_time": "19:30:00",
"язык": [
"Английский",
"Хинди",
"Панджаби"
] ,
"from_time": "10:30:00",
"create_at": "2023-11-03 18:05:16",
"updated_at": "2024-03-31 18:11:43"
} {
"id": 817,
"статус": 1,
"to_time": "17:30:00",
"язык": [
"Английский",
"Хинди"
],
"from_time" : "10:00:00",
"create_at": "2024-03-29 17:31:26",
"updated_at": null
}
Демо также показывает, как ускорить запрос с помощью индекса выражения GIN:
create index on tbl_lang using gin((data->'language') jsonb_ops);
Он доступен только для jsonb, но этого достаточно, чтобы alter table tbl_lang alter column "data" type jsonb using "data"::jsonb;. Столбец типа jsonb также легче и быстрее сканируется последовательно.
Для 110 тыс. случайных выборок вы можете перейти от времени выполнения 437ms для столбца с последовательным сканированием json к 42ms для последовательного сканирования в jsonb без индекса и к 22ms с использованием индексного сканирования в jsonb.
explain analyze verbose
select jsonb_pretty(data) from tbl_lang
where (data->'language') ?& array['English', 'Hindi'];