How to build a SQL query to find matches in a JSON string?

Question

MySQL

Good afternoon, there was such a very interesting task, you need to find records by coincidence, but the string is in JSON format.
When searching by ID, it finds several entries:

Displaying the result in a column

{"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":""}

After that, these records are sorted by% Ap%. The result will be only one entry:

Received data after sorting

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

The implementation of this method was as follows:

sql query with filter

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;

As you can see from the request, first the JSON string was formed, after which the required one was found using substring_index.

I would like to get rid of two selects in one request and somehow search for JSON by brute force. Yes, it would be possible to sort the data and return the result, but there is such a problem that there is LIMIT 50 OFFSET 0, which, as a result of the search, will display a limited number of records, and will search for 1 ml. records. Thus to receive result in 1 ml. records and sort it is not an option. What are the options dear experts?

Answer 1

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