Как правильно составить запрос для поиска по JSON полю в mySql?

Ссылка скопирована
JSON MySQL SQL
1 ответ

есть таблица 'action'. в ней есть поле типа JSON 'condition'.
структура данных в этом поле

[{     "data": [{"operator": "1"},{"type": "1"},{"1": {"values": "3"}}],     "action": "6",     "coupon": ["130"],     "discount": "10",     "change_cart": "1",     "discount_type": "0",     "change_price_after_use": "0",     "show_in_additional_items": "1"   },   {     "data": [{"operator": "1"},{"type": "2"},{"2": {"values": "77"}}],     "action": "6",     "coupon": ["130"],     "discount": "200",     "change_cart": "0",     "discount_type": "1",     "change_price_after_use": "1",     "show_in_additional_items": "1"   },   {     "data": [{"operator": "1"},{"type": "3"},{"3": {"values": "151262"}}],     "action": "6",     "coupon": ["130"],     "discount": "10",     "change_cart": "1",     "discount_type": "0",     "change_price_after_use": "0",     "show_in_additional_items": "0"   } ]

[{ "data": [{"operator": "1"},{"type": "1"},{"1": {"values": "3"}}], "action": "6", "coupon": ["130"], "discount": "10", "change_cart": "1", "discount_type": "0", "change_price_after_use": "0", "show_in_additional_items": "1" }, { "data": [{"operator": "1"},{"type": "2"},{"2": {"values": "77"}}], "action": "6", "coupon": ["130"], "discount": "200", "change_cart": "0", "discount_type": "1", "change_price_after_use": "1", "show_in_additional_items": "1" }, { "data": [{"operator": "1"},{"type": "3"},{"3": {"values": "151262"}}], "action": "6", "coupon": ["130"], "discount": "10", "change_cart": "1", "discount_type": "0", "change_price_after_use": "0", "show_in_additional_items": "0" } ]

Вопрос, как составить правильный запрос для выборки всех записей у которых в поле 'condition' есть "action": "6"?

Дополнительно:

а почему эти данные вообще в джейсоне лежат, а не в отдельной таблице?

  • FanatPHP, вопрос не в этом
  • Как раз в этом
    сначала от лени и неграмотности делаем из БД помойку, а потом "ой дядиньки напишите мне запрос, а то сами мы не местныя!"
  • при том что ответ гуглится за 30 секунд

  • структура данных в этом поле

    Это значение в одном поле одной записи? или это значение поля из трёх разных записей?

    Выложите CREATE TABLE (оставьте только поля id и condition), INSERT INTO с примером записей (3-5 записей, причём только некоторые соответствуют условию) и требуемый ответ для таких данных. Обязательно укажите точную версию MySQL.

  • FanatPHP, если бы такое гуглилось за 30сек. я бы не стал отвлекать столь компетентных и уважаемых пользователей как Вы, столь "глупыми" вопросами
  • Ну да, конечно. Не гуглится. Ну нету этой информации в интернете, правда же?
    Или, может быть, проблема не в отсуствии информации, а в прокладке между стулом и монитором, которая эту информацию даже и не пыталась искать? 
  • CREATE TABLE action( `id` int NOT NULL PRIMARY KEY AUTO_INCREMENT,  `condition` JSON );  INSERT INTO action (`condition`)  VALUES ('[{"data": [{"operator": "1"}, {"type": "1"}, {"1": {"values": "3"}}], "action": "6", "coupon": ["130"], "discount": "10", "change_cart": "1", "discount_type": "0", "change_price_after_use": "0", "show_in_additional_items": "1"}, {"data": [{"operator": "1"}, {"type": "2"}, {"2": {"values": "77"}}], "action": "6", "coupon": ["130"], "discount": "200", "change_cart": "0", "discount_type": "1", "change_price_after_use": "1", "show_in_additional_items": "1"}, {"data": [{"operator": "1"}, {"type": "3"}, {"3": {"values": "151262"}}], "action": "6", "coupon": ["130"], "discount": "10", "change_cart": "1", "discount_type": "0", "change_price_after_use": "0", "show_in_additional_items": "0"}]'), ('[{"action": ""}]'), ('[{"data": [{"operator": "1"}, {"type": "2"}, {"2": {"values": "24"}}, {"operator": "1"}, {"type": "1"}, {"1": {"values": "14"}}, {"operator": "1"}, {"type": "4"}, {"4": {"values": "1800"}}, {"operator": "1"}, {"type": "4"}, {"4": {"values": "1605"}}], "action": "5", "item_id": "165649", "discount": "1641", "show_in_list": "1", "discount_type": "1", "show_in_complect": "1", "show_in_additional_items": "1"}, {"data": [{"operator": "1"}, {"type": "2"}, {"2": {"values": "24"}}, {"operator": "1"}, {"type": "1"}, {"1": {"values": "14"}}, {"operator": "1"}, {"type": "4"}, {"4": {"values": "1800"}}, {"operator": "1"}, {"type": "4"}, {"4": {"values": "1609"}}, {"operator": "2"}, {"type": "3"}, {"3": {"values": "188603"}}], "action": "5", "item_id": "161922", "discount": "1641", "show_in_list": "1", "discount_type": "1", "show_in_complect": "1", "show_in_additional_items": "1"}, {"data": [{"operator": "1"}, {"type": "2"}, {"2": {"values": "24"}}, {"operator": "1"}, {"type": "1"}, {"1": {"values": "14"}}, {"operator": "1"}, {"type": "4"}, {"4": {"values": "1800"}}, {"operator": "1"}, {"type": "4"}, {"4": {"values": "1612"}}], "action": "5", "item_id": "161923", "discount": "2031", "show_in_list": "1", "discount_type": "1", "show_in_complect": "1", "show_in_additional_items": "1"}, {"data": [{"operator": "1"}, {"type": "2"}, {"2": {"values": "24"}}, {"operator": "1"}, {"type": "1"}, {"1": {"values": "14"}}, {"operator": "1"}, {"type": "4"}, {"4": {"values": "1800"}}, {"operator": "1"}, {"type": "4"}, {"4": {"values": "1617"}}, {"operator": "2"}, {"type": "3"}, {"3": {"values": "175359"}}], "action": "5", "item_id": "161929", "discount": "2551", "show_in_list": "1", "discount_type": "1", "show_in_complect": "1", "show_in_additional_items": "1"}, {"data": [{"operator": "1"}, {"type": "2"}, {"2": {"values": "24"}}, {"operator": "1"}, {"type": "1"}, {"1": {"values": "14"}}, {"operator": "1"}, {"type": "4"}, {"4": {"values": "1800"}}, {"operator": "1"}, {"type": "4"}, {"4": {"values": "1621"}}], "action": "5", "item_id": "161931", "discount": "3201", "show_in_list": "1", "discount_type": "1", "show_in_complect": "1", "show_in_additional_items": "1"}, {"data": [{"operator": "1"}, {"type": "2"}, {"2": {"values": "24"}}, {"operator": "1"}, {"type": "1"}, {"1": {"values": "14"}}, {"operator": "1"}, {"type": "4"}, {"4": {"values": "1800"}}, {"operator": "1"}, {"type": "4"}, {"4": {"values": "1624"}}], "action": "5", "item_id": "161932", "discount": "3851", "show_in_list": "1", "discount_type": "1", "show_in_complect": "1", "show_in_additional_items": "1"}, {"data": [{"operator": "1"}, {"type": "2"}, {"2": {"values": "24"}}, {"operator": "1"}, {"type": "1"}, {"1": {"values": "14"}}, {"operator": "1"}, {"type": "4"}, {"4": {"values": "1800"}}, {"operator": "1"}, {"type": "4"}, {"4": {"values": "1626"}}], "action": "5", "item_id": "161933", "discount": "4441", "show_in_list": "1", "discount_type": "1", "show_in_complect": "1", "show_in_additional_items": "1"}]'), ('[{"data": [{"operator": "1"}, {"type": "1"}, {"1": {"values": "3"}}], "action": "6", "coupon": ["130"], "discount": "10", "change_cart": "1", "discount_type": "0", "change_price_after_use": "0", "show_in_additional_items": "1"}, {"data": [{"operator": "1"}, {"type": "2"}, {"2": {"values": "77"}}], "action": "6", "coupon": ["130"], "discount": "200", "change_cart": "0", "discount_type": "1", "change_price_after_use": "1", "show_in_additional_items": "1"}, {"data": [{"operator": "1"}, {"type": "3"}, {"3": {"values": "151262"}}], "action": "6", "coupon": ["130"], "discount": "10", "change_cart": "1", "discount_type": "0", "change_price_after_use": "0", "show_in_additional_items": "0"}]'), ('[{"action": ""}]');

    CREATE TABLE action( `id` int NOT NULL PRIMARY KEY AUTO_INCREMENT, `condition` JSON ); INSERT INTO action (`condition`) VALUES ('[{"data": [{"operator": "1"}, {"type": "1"}, {"1": {"values": "3"}}], "action": "6", "coupon": ["130"], "discount": "10", "change_cart": "1", "discount_type": "0", "change_price_after_use": "0", "show_in_additional_items": "1"}, {"data": [{"operator": "1"}, {"type": "2"}, {"2": {"values": "77"}}], "action": "6", "coupon": ["130"], "discount": "200", "change_cart": "0", "discount_type": "1", "change_price_after_use": "1", "show_in_additional_items": "1"}, {"data": [{"operator": "1"}, {"type": "3"}, {"3": {"values": "151262"}}], "action": "6", "coupon": ["130"], "discount": "10", "change_cart": "1", "discount_type": "0", "change_price_after_use": "0", "show_in_additional_items": "0"}]'), ('[{"action": ""}]'), ('[{"data": [{"operator": "1"}, {"type": "2"}, {"2": {"values": "24"}}, {"operator": "1"}, {"type": "1"}, {"1": {"values": "14"}}, {"operator": "1"}, {"type": "4"}, {"4": {"values": "1800"}}, {"operator": "1"}, {"type": "4"}, {"4": {"values": "1605"}}], "action": "5", "item_id": "165649", "discount": "1641", "show_in_list": "1", "discount_type": "1", "show_in_complect": "1", "show_in_additional_items": "1"}, {"data": [{"operator": "1"}, {"type": "2"}, {"2": {"values": "24"}}, {"operator": "1"}, {"type": "1"}, {"1": {"values": "14"}}, {"operator": "1"}, {"type": "4"}, {"4": {"values": "1800"}}, {"operator": "1"}, {"type": "4"}, {"4": {"values": "1609"}}, {"operator": "2"}, {"type": "3"}, {"3": {"values": "188603"}}], "action": "5", "item_id": "161922", "discount": "1641", "show_in_list": "1", "discount_type": "1", "show_in_complect": "1", "show_in_additional_items": "1"}, {"data": [{"operator": "1"}, {"type": "2"}, {"2": {"values": "24"}}, {"operator": "1"}, {"type": "1"}, {"1": {"values": "14"}}, {"operator": "1"}, {"type": "4"}, {"4": {"values": "1800"}}, {"operator": "1"}, {"type": "4"}, {"4": {"values": "1612"}}], "action": "5", "item_id": "161923", "discount": "2031", "show_in_list": "1", "discount_type": "1", "show_in_complect": "1", "show_in_additional_items": "1"}, {"data": [{"operator": "1"}, {"type": "2"}, {"2": {"values": "24"}}, {"operator": "1"}, {"type": "1"}, {"1": {"values": "14"}}, {"operator": "1"}, {"type": "4"}, {"4": {"values": "1800"}}, {"operator": "1"}, {"type": "4"}, {"4": {"values": "1617"}}, {"operator": "2"}, {"type": "3"}, {"3": {"values": "175359"}}], "action": "5", "item_id": "161929", "discount": "2551", "show_in_list": "1", "discount_type": "1", "show_in_complect": "1", "show_in_additional_items": "1"}, {"data": [{"operator": "1"}, {"type": "2"}, {"2": {"values": "24"}}, {"operator": "1"}, {"type": "1"}, {"1": {"values": "14"}}, {"operator": "1"}, {"type": "4"}, {"4": {"values": "1800"}}, {"operator": "1"}, {"type": "4"}, {"4": {"values": "1621"}}], "action": "5", "item_id": "161931", "discount": "3201", "show_in_list": "1", "discount_type": "1", "show_in_complect": "1", "show_in_additional_items": "1"}, {"data": [{"operator": "1"}, {"type": "2"}, {"2": {"values": "24"}}, {"operator": "1"}, {"type": "1"}, {"1": {"values": "14"}}, {"operator": "1"}, {"type": "4"}, {"4": {"values": "1800"}}, {"operator": "1"}, {"type": "4"}, {"4": {"values": "1624"}}], "action": "5", "item_id": "161932", "discount": "3851", "show_in_list": "1", "discount_type": "1", "show_in_complect": "1", "show_in_additional_items": "1"}, {"data": [{"operator": "1"}, {"type": "2"}, {"2": {"values": "24"}}, {"operator": "1"}, {"type": "1"}, {"1": {"values": "14"}}, {"operator": "1"}, {"type": "4"}, {"4": {"values": "1800"}}, {"operator": "1"}, {"type": "4"}, {"4": {"values": "1626"}}], "action": "5", "item_id": "161933", "discount": "4441", "show_in_list": "1", "discount_type": "1", "show_in_complect": "1", "show_in_additional_items": "1"}]'), ('[{"data": [{"operator": "1"}, {"type": "1"}, {"1": {"values": "3"}}], "action": "6", "coupon": ["130"], "discount": "10", "change_cart": "1", "discount_type": "0", "change_price_after_use": "0", "show_in_additional_items": "1"}, {"data": [{"operator": "1"}, {"type": "2"}, {"2": {"values": "77"}}], "action": "6", "coupon": ["130"], "discount": "200", "change_cart": "0", "discount_type": "1", "change_price_after_use": "1", "show_in_additional_items": "1"}, {"data": [{"operator": "1"}, {"type": "3"}, {"3": {"values": "151262"}}], "action": "6", "coupon": ["130"], "discount": "10", "change_cart": "1", "discount_type": "0", "change_price_after_use": "0", "show_in_additional_items": "0"}]'), ('[{"action": ""}]');

  • FanatPHP, вместо того чтобы самоутверждаться за счет других, лучше бы помог. А если нужно выговориться заведи себе собаку или кота и им жалуйся, на тупых юзеров.
    Если ты за 30сек гугления нашел ответ на данный вопрос или то, что не нагуглил я, то, будь любезен, поделись ссылками. Буду признателен
  • Тише, тише, горячие эстонские парни! :-)))

    FanatPHP, конечно, любит похамить, но ссылочку я действительно нашел с первой же попытки, поискав mysql search in json array.

    И на предмет подумать над архитектурой он тоже прав. Поиск в джейсоне - и так не самое производительное дело, а тут еще и поиск в массиве, да не значения, а поля в объекте... ноги сломаешь.

    Так что в данном конкретном случае стоит серьезнейшим образом подумать не над тем, как запрос написать, а над тем, как поменять эту безумную конструкцию на что-то более удобоваримое.

  • BorLaze, спасибо, но первую страницу гугловской выдачи я безуспешно просмотрел.
    Что касается архитектуры, то она была такая, еще до моего прихода на проект
  • topalek, ну, надо было вторую глянуть... 
    SELECT * FROM action WHERE json_contains(`condition`->'$[*].action', json_array("6"));

    SELECT * FROM action WHERE json_contains(`condition`->'$[*].action', json_array("6"));

    вродь бы ищет.

    Но... план я не смотрел, но и без этого можно с уверенностью предсказать - поиск хотя бы по тысяче записей отправит машину в долгую задумчивость. Две функции в условии, да плюс проход по массиву...

    А то, что архитектура "была такая, еще до моего прихода на проект" - еще вовсе не повод ее переделать, когда пришла нужда.

    Можно сбить собачью конуру из досок, можно конуру увеличивать, но когда она вырастает до размеров дома, без фундамента уже не обойтись.

  • topalek, а требуемый ответ и версию MySQL мы увидим?
  • Akina, ой. извините, завтыкал MySQL 8.0
  • BorLaze, спасибо огромное. работает)))
  • BorLaze,

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

    Это Вы зря. JSON хранится и обрабатывается во внутреннем бинарном формате, так что особых тормозов от этого запроса не ожидается.

  • Akina, ну, как там в MySQL, не знаю, сужу по postgres.

    А там с этим далеко не просто... есть простой json, есть бинарный jsonb... Пока не проиндексируешь нужные поля объекта - будет full scan. Да и индексы под задачи подбирать надо...

    В общем, я бы не рискнул утверждать "особых тормозов от этого запроса не ожидается".
    Впрочем, в любом случае надо план смотреть.

    • Ответы:

    Как вариант использовать LIKE:

    SELECT * FROM `action` WHERE `condition` LIKE '%"action": "6"%';

    SELECT * FROM `action` WHERE `condition` LIKE '%"action": "6"%';

    Test SQL query online

    • спасибо, и этот вариант работает))
    • topalek, учтите, что этот вариант жёстко завязан на структуру JSON. Если action по смыслу число, то клиентскому софту параллельно, числовое или строковое будет значение, а вот этот запрос на числовом значении споткнётся.
    • ...работает, пока условие {"action": "6"} одно и находится в нужном месте

      стоит вложить его внутрь еще какого-нибудь поля - суши весла.

    • этот вариант работает с учетом бага в MySQL 
      SELECT * FROM `action` WHERE JSON_SEARCH(`condition`, 'one', '6') IS NOT NULL;

      SELECT * FROM `action` WHERE JSON_SEARCH(`condition`, 'one', '6') IS NOT NULL;

      Но если у Вас MariaDB - используйте смело

    запрос для выборки всех записей у которых в поле 'condition' есть "action": "6"

    SELECT DISTINCT action.* FROM action CROSS JOIN JSON_TABLE(action.`condition`,                       '$[*].action' COLUMNS (action INT PATH '$')) jsontable WHERE jsontable.action = 6

    SELECT DISTINCT action.* FROM action CROSS JOIN JSON_TABLE(action.`condition`, '$[*].action' COLUMNS (action INT PATH '$')) jsontable WHERE jsontable.action = 6

    https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=c3e97c...

    Нужно решить такую задачу?

    Опишите проблему, и специалист поможет с настройкой, исправлением ошибки или доработкой сайта. Подберём понятный план работ без лишней переписки.

    Заказать помощь
    Лучший ответ
    1
    Андрей PHP Ответ

    Для того чтобы правильно составить запрос для поиска по JSON полю в MySQL, можно использовать функцию JSON_EXTRACT(). Эта функция позволяет извлекать данные из JSON объекта и сравнивать их со значением, которое вы ищете.

    Вот пример запроса на языке SQL, который показывает как выполнить поиск по JSON полю в MySQL:

    SELECT *
FROM your_table
WHERE JSON_EXTRACT(your_json_column, '$.your_key') = 'your_value';

    SELECT * FROM your_table WHERE JSON_EXTRACT(your_json_column, '$.your_key') = 'your_value';

    В данном примере your_table - это таблица, в которой находится JSON поле, your_json_column - это само JSON поле, your_key - это ключ в JSON объекте, по которому вы хотите выполнить поиск, и your_value - значение, которое вы ищете по данному ключу.

    Если у вас есть JSON объект вида {"name": "John", "age": 30}, и вы хотите найти все записи, где значение ключа "name" равно "John", то ваш запрос будет выглядеть следующим образом:

    SELECT *
FROM your_table
WHERE JSON_EXTRACT(your_json_column, '$.name') = 'John';

    SELECT * FROM your_table WHERE JSON_EXTRACT(your_json_column, '$.name') = 'John';

    Таким образом, используя функцию JSON_EXTRACT() вы сможете эффективно выполнять поиск по JSON полям в MySQL. Не забудьте заменить your_table, your_json_column, your_key и your_value на соответствующие значения из вашей базы данных.

    Другие ответы (0)

    Пока нет других ответов. Будьте первым, кто поможет автору.

    Ответить на вопрос

    комментарий

    Ваш адрес email не будет опубликован. Обязательные поля помечены *

    Вам также может быть интересно

    VPN Как правильно настроить vless для Android TV? 1 ответ Pyrogram Как правильно зарегистрировать юзер бота в Telegram? 1 ответ печатные-платы Как заставить запускаться программу M3.exe от компании Hanxing AOI в инспекционной машине на Windows 7 Pro? 1 ответ Аккумуляторные батареи Почему при зарядке автостарта слышен писк, где искать причину? 1 ответ