How to search for information in a SQL database in a JSON format column?

Question

PHP

Good day!
Please help me solve the problem. There are two columns in the DB (MySQL version: 5.7.26-29):
1 column

{
    "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 column

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

How to search for records with the desired values? For example:
SEARCH: gender = men AND age_to < 13 AND age_from > 9
SEARCH: gender = woman AND additionally = yes
SEARCH: Bone pain

Answer 1

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

Answer 2

You can work with JSON normally in MySQL, you just need to choose data structures suitable for searching, and not like in the 1st column (associative array).
Each such object in the 1st column

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

must be stored in a separate record so that they can be searched.
In general, you need to search using JSON Path Syntax (read the documentation), like this:

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

So whenever possible it is worth redesigning the tables. Indexes must be added to all fields that require searching, unless, of course, the table has a size of 100 records.

SEARCH: Bone pain

This is a full text search.
In short, you can't just put any JSON in the DBMS and rely on its capabilities. It is necessary to initially design a database for writing and reading with different types of queries.

Answer 3

Never store such data in json format.
Store in the normal form, choose the usual where

Answer 4

Thank you all for your advice, probably really need to redo the database structure.
But there are a number of questions:
1) If there are two tables, the query, as far as I understand, is this:

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

And how to get all records from related tables in one query if there are 10 tables, for example?
2) What about duplicate column names (id, parent_id, name, date, etc.)?
In order to display the result in variables like $row['name'] , is it necessary that the names of the columns in the related tables are not duplicated? Or is there another solution?
3) If in one of the linked tables you need to get not one row, but several, what should you do?