?? ?????? ????? ????? ???? ???? ?? ???? ?????. ?? 5.7? ??? MySQL? JSON ??? ??? ?????? ??? ??? ???? ??, ?? ? ???? ??? ??? ?????. ? ?????? ???? ? ??? ?? ?? ??? ?? MySQL? ???? ?? JSON ??? ?????.

MySQL?? JSON? ???? ??? ??????

JSON ???? ??? ??????? ???? ???? ?? ??? ??? ??? ???? ? ????. ??? ?????:

• ???: JSON? ?? ? ??? ??? ??? ?????.
• ?? ??: JSON ???? ????? ??, ???? ? ?????.
• ??: ????? ?? ??? ?? ??? ???? JSON ??? ?????.

1. JSON ??? ??

JSON_OBJECT() ? JSON_ARRAY() ??? ???? JSON ?? ?? ??? ??? ? ????.

?:

SELECT 
    JSON_OBJECT('id', 1, 'name', 'Alice', 'roles', JSON_ARRAY('admin', 'editor')) AS json_data;

??:

{"id": 1, "name": "Alice", "roles": ["admin", "editor"]}

2. JSON ??? ??

JSON ???? ????? ?? JSON ??? ??? ?????.

?:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    details JSON NOT NULL
);

INSERT INTO users (details) 
VALUES ('{"name": "Bob", "age": 30, "roles": ["viewer", "editor"]}');

3. JSON?? ??? ??

MySQL? JSON ???? ???? ???? ?? ??? ?????.

• JSON_EXTRACT(): JSON ??? ???? ?? ?????.
• -> ???: JSON_EXTRACT()? ?????.

?:

SELECT 
    JSON_EXTRACT(details, '$.name') AS name,
    details->'$.age' AS age
FROM users;

??:

4. JSON ??? ??

??? ???? JSON ???? ??? ??????? ??? ? ????.

• JSON_SET(): ?? ????? ???????.
• JSON_INSERT(): ?? ??? ?????.
• JSON_REPLACE(): ?? ?? ???????.

?:

UPDATE users
SET details = JSON_SET(details, '$.city', 'New York')
WHERE id = 1;

SELECT details FROM users;

??:

{"name": "Bob", "age": 30, "roles": ["viewer", "editor"], "city": "New York"}

5. ? ?? ? ??

JSON_REMOVE()? ???? JSON ???? ??? ?????.

?:

UPDATE users
SET details = JSON_REMOVE(details, '$.roles')
WHERE id = 1;

SELECT details FROM users;

??:

{"name": "Bob", "age": 30, "city": "New York"}

6. JSON?? ????

JSON_CONTAINS() ??? JSON ??? ?? ?? ???? ??? ?????.

?:

SELECT 
    JSON_CONTAINS(details, '"New York"', '$.city') AS has_city
FROM users;

??:

7. JSON ??? ??

JSON_ARRAYAGG() ? JSON_OBJECTAGG() ??? ?? ??? JSON ??? ???? ? ??? ???.

?:

SELECT 
    JSON_ARRAYAGG(name) AS names
FROM (
    SELECT JSON_EXTRACT(details, '$.name') AS name FROM users
) AS subquery;

??:

SELECT 
    JSON_OBJECT('id', 1, 'name', 'Alice', 'roles', JSON_ARRAY('admin', 'editor')) AS json_data;

8. JSON ??? ??? ??

JSON_VALID() ??? ???? ??? JSON?? ?????.

?:

{"id": 1, "name": "Alice", "roles": ["admin", "editor"]}

??:

9. JSON? ??? ????

??? ?? ? ?? JSON ??? ????? JSON_PRETTY()? ?????.

?:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    details JSON NOT NULL
);

INSERT INTO users (details) 
VALUES ('{"name": "Bob", "age": 30, "roles": ["viewer", "editor"]}');

??:

SELECT 
    JSON_EXTRACT(details, '$.name') AS name,
    details->'$.age' AS age
FROM users;

?? JSON ??

MySQL? ??? ??? ? ??? ??? JSON ??? ?????. ??? ?? JSON ?????.

• JSON_ARRAY_APPEND(): JSON ?? ?? ?? ?????.
• JSON_ARRAY_INSERT(): JSON ??? ??? ??? ?? ?????.
• JSON_CONTAINS_PATH(): JSON ?? ?? ??? ??? ????? ?????.
• JSON_DEPTH(): JSON ??? ?? ??? ?????.
• JSON_KEYS(): JSON ???? ?? ?????.
• JSON_LENGTH(): JSON ??? ?? ? ?? JSON ??? ? ?? ?????.
• JSON_MERGE_PATCH(): ?? JSON ??? ???? ?? ?? ?? ??? ?????.
• JSON_MERGE_PRESERVE(): ?? JSON ??? ???? ??? ?? ??? ???? ?? ?? ?????.
• JSON_OVERLAPS(): ? JSON ??? ??? ??? ??? ?????.
• JSON_QUOTE(): ???? ?? ??? ??????? ??? JSON ??? ???? ?????.
• JSON_SEARCH(): JSON ?? ??? ??? ?? ?? ??? ?????.
• JSON_STORAGE_FREE(): JSON ?? ??? ? ??? ??? ?? ?????.
• JSON_STORAGE_SIZE(): JSON ??? ?? ??? ??? ??? ?????.
• JSON_TABLE(): ?? ??? ?? JSON ???? ??? ??? ???? ?????.
• JSON_TYPE(): JSON ?? ??? ?????(?: ??, ??, ??? ?).
• JSON_UNQUOTE(): JSON ????? ???? ?????.

MySQL? JSON ??? ??? ???????? ????? ???? ???? ?? ??? ??? ?????. ?? ?? SQL ??? ?? JSON? ?? ??, ?? ? ??? ? ????. ??? ??? ???? ????? ????? ?????? ??? ?? ??? ?? ??? ?? ? ??? ? ? ????.

? ??? MySQL?? JSON ??? ?? ?????. ??? ??? PHP ??? ????? ?? ?? ??? ?????!