Как построить SQL запрос по поиску совпадений в JSON строке?

Question

[Galdar Turin]

Добрый день, появилась такая очень интересная задача, надо найти записи по совпадению, но строка в формате JSON.
При поиски по идентификатору, находит несколько записей:

Вывод результата в столбце

{"id":"1000004688","ap_name":"Яблоко","ap_city_id":"","itn":"","kpp":"","okpo":""}
{"id":"1000004688","ap_name":"Апельсин","ap_city_id":"","itn":"","kpp":"","okpo":""}
{"id":"1000004688","ap_name":"Арбуз","ap_city_id":"","itn":"","kpp":"","okpo":""}

После чего по этим записям идет сортировка по %Aп%. В результате будет получена только одна запись:

Полученные данные после сортировки

{"id":"1000004688","ap_name":"Апельсин","ap_city_id":"","itn":"","kpp":"","okpo":""}

Реализация такого способа была такая:

sql запрос с фильтром

SELECT contragent , 
COUNT(*) OVER() as `total_count` FROM ( 
	SELECT  `contragents`.`id`, 
	CONCAT_WS('', '{\"id\":\"', A.`id`,'\"', ',\"ap_name\":\"', A.`ap_name`,'\"', ',\"ap_city_id\":\"', AA.`name_ru`,'\"', ',\"itn\":\"', A.`itn`,'\"', ',\"kpp\":\"', A.`kpp`,'\"', ',\"okpo\":\"', A.`okpo`,'\"','}') AS `entpr_id`, 
	CONCAT_WS('', '{\"id\":\"', B.`id`,'\"', ',\"ap_name\":\"', B.`ap_name`,'\"', ',\"ap_city_id\":\"', AB.`name_ru`,'\"', ',\"itn\":\"', B.`itn`,'\"', ',\"kpp\":\"', B.`kpp`,'\"', ',\"okpo\":\"', B.`okpo`,'\"','}') AS `contragent`, 
	`contragents`.`type`, `contragents`.`ctype`, `contragents`.`resp`, `contragents`.`author`, 
	`contragents`.`code`, `contragents`.`contract`, `contragents`.`owner`, 
	COUNT(*) OVER() as `total_count`  
	FROM `contragents`  
	LEFT JOIN `companies` A on A.`id` = `contragents`.`entpr_id` 
	LEFT JOIN `companies` B on B.`id` = `contragents`.`contragent` 
	LEFT JOIN `cities` AA on AA.`id` = `A`.`ap_city_id` 
	LEFT JOIN `cities` AB on AB.`id` = `B`.`ap_city_id` 
	WHERE      
	`contragents`.`id` IS NOT NULL  AND  ( `contragents`.`entpr_id` IN (1000004688) ) 
) AS res 
WHERE contragent 
LIKE '%Ап%' ORDER BY substring_index(contragent,'\"ap_name\":',-1) LIMIT 50 OFFSET 0;

Как видно из запроса, сначала JSON строка была сформирована, после чего с использованием substring_index была найдена нужная.

Хотелось бы избавится от двух селектов в одном запросе и каким то образом искать по JSON путем перебора. Да можно было бы получить данные отсортировать и выдать результат, но тут такая проблема что есть LIMIT 50 OFFSET 0, который в результате поиска выведет ограниченное число записей, а искать будет по 1мл. записей. Таким образом получать результат в 1мл. записей и сортировать его не вариант. Какие есть варианта дорогие знатоки?

Comments

[Ипатьев]

А ведь всё началось с того, что какой-то гений посоветовал автору вопроса вместо нормальной структуры таблиц использовать джейсон.
причем небось здесь же, на хабре

[Galdar Turin]

Роман Юрьевич Ипатьев, не)) Это уже было до меня)) Я немного удивился с такого подхода и хранения, но кому-то было виднее))

[Ипатьев]

Можно не удивляться, а просто переписать в нормальную форму

[Galdar Turin]

Роман Юрьевич Ипатьев, да там все надо переделывать тогда) Ну за то интересно.

[ThunderCat]

Роман Юрьевич Ипатьев, Вроде там нормальная структура, просто в ответе возвращает строку в жсон формате, в полях вроде обычные скаляры/варчар... Зачем - это другой большой вопрос, но так то структура в порядке...

[Ипатьев]

ThunderCat, да, со структурой таблицы, уже ниже разобрались.
Тут уже запрос вызывает изумление

Answer 1

[Александр Аксентьев]

https://dev.mysql.com/doc/refman/8.0/en/json-searc...

Comments

[Galdar Turin]

Александр Аксентьев, а как мне из

SELECT  
	CONCAT_WS('', 
		'{\"id\":\"', A.`id`,'\"', ',\"ap_name\":\"', 
		A.`ap_name`,'\"', ',\"ap_city_id\":\"', AA.`name_ru`,
		'\"', ',\"itn\":\"', A.`itn`,'\"', ',\"kpp\":\"', 
		A.`kpp`,'\"', ',\"okpo\":\"', A.`okpo`,'\"','}'
	) AS `entpr_id`,

получить собранный JSON entpr_id, что бы использовать его в WHERE?

[Ипатьев]

До меня только сейчас дошло, что джейсон не хранится в БД, а сначала собирается в запросе. А потом по нему ищем и сортирует
Это гениально.
на govnokod.ru оторвут с руками

[Александр Аксентьев]

Роман Юрьевич Ипатьев, а я тоже как-то не увидел даже.

Galdar Turin, так зачем вообще ковыряться в этом jsonе, если можно искать в нужной колонке сразу...
A.`ap_name` и B.`ap_name`

[Akina]

Galdar Turin, Вы что, ещё и этот JSON "руками" собираете? а тогда вообще нафига все эти заморочки? Что мешает использовать скалярные значения полей напрямую, не собирая их в JSON?

[Galdar Turin]

Александр Аксентьев, да, в принципе можно итак искать, JSON потом выводится как результат из нескольких таблиц

[Galdar Turin]

Роман Юрьевич Ипатьев, просто как быть иначе если человеку нужно выводить результат из нескольких таблиц?

[Ипатьев]

Брать и выводить, обычным SQL. Без всяких джейсонов. сократив его раз в 10.
Сейчас этот запрос что-то вроде машины руба голдберга

[irishmann]

Роман Юрьевич Ипатьев, месье знает толк в извращениях

[Galdar Turin]

Роман Юрьевич Ипатьев, орнул))

...машины руба голдберга