Как искать информацию в SQL базе в колонке JSON формата?

Question

[forwox]

Доброго времени суток!
Помогите пожалуйста решить задачку. Есть два столбца в БД (Версия MySQL: 5.7.26-29):
1 столбец

{
    "1": {
        "age_to": "8",
        "gender": "children",
        "age_from": "4",
        "quantity": "400",
        "additionally": "no"
    },
    "2": {
        "age_to": "13",
        "gender": "men",
        "age_from": "9",
        "quantity": "600",
        "additionally": "no"
    },
    "3": {
        "age_to": "70",
        "gender": "men",
        "age_from": "14",
        "quantity": "900",
        "additionally": "no"
    },
    "4": {
        "age_to": "13",
        "gender": "woman",
        "age_from": "9",
        "quantity": "600",
        "additionally": "yes"
    }
}

2 столбец

[
    {
        "value": "Боль в животе",
        "color": "red",
    },
    {
        "value": "Боль в костях",
        "color": "blue",
    } 
]

Как искать записи с нужными значениями? Например такие :
SEARCH: gender = men AND age_to < 13 AND age_from > 9
SEARCH: gender = woman AND additionally = yes
SEARCH: Боль в костях

Comments

[Daria Motorina]

Описание такого рода вопросов нужно начинать с указания контретной бд и ее версии, потому что средства работы с json есть далеко не у всех, ну а по-хорошему такие данные парсят и хранят в нормализованном виде в реляционной бд или переносят в nosql базу

[forwox]

Daria Motorina, Версия MySQL: 5.7.26-29
На счёт "реляционной бд" спасибо за подсказку, почитаю подробнее.

[Everything_is_not_so_bad]

"gender": "children",

это что за пол такой?

[forwox]

Роман Мирр, дети ) Не вижу смысла дублировать две одинаковые записи с разным gender. т.к. дети относятся к одной группе

Answer 1

[Dimonchik]

https://dev.mysql.com/doc/refman/5.7/en/json.html
https://dev.mysql.com/doc/refman/8.0/en/json.html

Answer 2

[Everything_is_not_so_bad]

С JSON можно нормально работать в MySQL, только надо выбирать подходящие для поиска структуры данных, а не как в 1-м столбце (ассоциативный массив).
Каждый такой объект в 1-м столбце

{
        "age_to": "8",
        "gender": "children",
        "age_from": "4",
        "quantity": "400",
        "additionally": "no"
    }

надо хранить в отдельной записи для возможности поиска по ним.
В целом, искать нужно при помощи JSON Path Syntax (читать документацию), например так:

SELECT * FROM table WHERE column ->> '$.a.b' = 'bla-bla'

Так что по возможности стоит перепроектировать таблицы. На все поля, требующие поиска, нужно добавить индексы, если, конечно, таблица не размером в 100 записей.

SEARCH: Боль в костях

Это уже полнотекстовый поиск.
Короче, нельзя просто класть любой JSON в СУБД и полагаться на её возможности. Нужно изначально проектировать БД для записи и чтения с разными видами запросов.

Answer 3

[FanatPHP]

Никогда не хранить такие данные в формате json.
Хранить в нормальном виде, выбирать обычным where

Answer 4

[forwox]

Всем спасибо за советы, наверно и в правду нужно переделать структуру БД.

Но есть ряд вопросов:
1) Если таблицы две, запрос на сколько я понимаю вот такой:

$row = $db->super_query( "SELECT * FROM name_table p LEFT JOIN name_table_two n ON (p.id=n.parent_id) WHERE........

А как получить все записи из связанных таблиц в одном запросе если таблиц например 10 штук?

2) Как быть с повторяющими названиями столбцов (id, parent_id, name, date и т.п.)?
Что бы выводить результат в переменных вида $row['name'], обязательно нужно что бы названия столбцов в связанных таблицах не дублировались? Или есть другое решение?

3) Если в одной из связанных таблиц нужно получить не одну строку а несколько, как быть?

Comments

[shurshur]

Не надо ещё один вопрос задавать в ответах. Во-первых, это не ответ на указанный вопрос, во-вторых, его тут никто не увидит. Лучше сформулировать новый вопрос.