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

Question

[topalek]

есть таблица '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"
  }
]

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

Comments

[FanatPHP]

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

[topalek]

FanatPHP, вопрос не в этом

[FanatPHP]

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

[FanatPHP]

при том что ответ гуглится за 30 секунд

[Akina]

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

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

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

[topalek]

FanatPHP, если бы такое гуглилось за 30сек. я бы не стал отвлекать столь компетентных и уважаемых пользователей как Вы, столь "глупыми" вопросами

[FanatPHP]

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

[topalek]

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": ""}]');

[topalek]

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

[BorLaze]

Тише, тише, горячие эстонские парни! :-)))

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

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

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

[topalek]

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

[BorLaze]

topalek, ну, надо было вторую глянуть...

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

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

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

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

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

[Akina]

topalek, а требуемый ответ и версию MySQL мы увидим?

[topalek]

Akina, ой. извините, завтыкал MySQL 8.0

[topalek]

BorLaze, спасибо огромное. работает)))

[Akina]

BorLaze,

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

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

[BorLaze]

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

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

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

Answer 1

[Slava Rozhnev]

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

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

Test SQL query online

Comments

[topalek]

спасибо, и этот вариант работает))

[Akina]

topalek, учтите, что этот вариант жёстко завязан на структуру JSON. Если action по смыслу число, то клиентскому софту параллельно, числовое или строковое будет значение, а вот этот запрос на числовом значении споткнётся.

[BorLaze]

...работает, пока условие {"action": "6"} одно и находится в нужном месте

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

[Slava Rozhnev]

этот вариант работает с учетом бага в MySQL

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

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

Answer 2

[Akina]

запрос для выборки всех записей у которых в поле 'condition' есть "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...