Как осуществить поиск (like) по полю в массиве в json колонке?

Question

[Вячеслав Шевченко]

Есть поле phones которое содержит похожие данные:

[
{"field": "Дополнительное поле", "phone": "70000000001"}, 
{"field": "Внутренний 999", "phone": "70000000002"}, 
{"field": null, "phone": "70000000003"}
]

Как можно выполнить like запрос по полю phone в каждом массиве?

Comments

[Виктор]

окей гугл

[Вячеслав Шевченко]

vhood, нет это не подходит т.к. в колонке хранится объект и поиск идет по конкретному полю данного объекта, а у меня в колонке массив и нужно искать по всем элементам данного массива, по конкретному полю т.е. phones->N->phone like '%001%'.

[Виктор]

Вячеслав Шевченко, не проблема, можно погуглить еще

[Everything_is_bad]

Вячеслав Шевченко, как только возникает поиск, индекс и подобное по json, лучше сразу нормализовать данные, с "простым" json еще можно поизвращаться, но дальше это только лишние проблемы. Либо использовать внешние сервисы поиска, типа elasticsearch

Answer 1

[alexalexes]

Сделать нормализацию структуры базы.
Перенести JSON в таблицу user_phone.
Поля:
phone_id, -- первичный ключ телефона
user_id, -- внешний ключ, кому относится телефон
phone, -- телефон
phone_comment, -- комментарий к телефону
-- еще поля по вкусу, но иногда выручающие
is_main, -- основной не основной/порядок приоритета
add_date -- дата внесения телефона
И в запросах уже нормально джойнить и лайкать эту таблицу.
PS:
В качестве временного костыля (ни в коем случае не оставлять на постоянной основе!):

SELECT Users.*,
       ph.value->>'phone' as phone
FROM Users, json_array_elements(Users.phones) as ph
where ph.value->>'phone'   like '7%3';

Comments

[Вячеслав Шевченко]

Мне самому такой подход сильно ближе, но я на проект только заступил и так было сделано и человеку, кторый это сделал нравится то, что с минимальным кол-вом движений можно добавить поле. Но как по мне с выборкой прям много сложностей. Хотя возможно надо прям погрузится, изучить работу с json.

[alexalexes]

Будет еще больше сложностей, когда внедрите extract-json - технический долг будет только нарастать. Лучше переделать по уму, технический долг исчезнет.

[alexalexes]

Добавил пример, как делать не надо.