Робота з даними JSON у SQL: Сучасний підхід

Оскільки сучасні додатки все більше залежать від напівструктурованих даних, JSON (JavaScript Object Notation) став популярним форматом для зберігання даних. Багато реляційних баз даних тепер підтримують зберігання, запити та маніпулювання даними JSON безпосередньо, поєднуючи гнучкість NoSQL із надійністю SQL.

У цій статті ми розглянемо, як працювати з даними JSON у SQL, охоплюючи основні функції, варіанти використання та практичні приклади.

Чому варто використовувати JSON у SQL?

1. Гнучкі структури даних: JSON дозволяє зберігати вкладені та динамічні дані.
2. Еволюція схеми: JSON може адаптуватися до змін без необхідності змінювати схеми таблиць.
   3.
   Інтеграція з API: JSON спрощує взаємодію з RESTful API, які повертають або приймають JSON-вантажі.

Зберігання даних JSON у SQL

Багато баз даних підтримують рідний тип даних JSON, що дозволяє перевіряти та оптимізувати зберігання JSON.

Приклад таблиці

CREATE TABLE orders (  
 order_id INT PRIMARY KEY,  
 customer_details JSON,  
 order_date DATE  
);

Приклад даних:

INSERT INTO orders (order_id, customer_details, order_date)  
VALUES  
(1, '{"name": "Alice", "email": "[email protected]", "address": {"city": "New York", "zip": "10001"}}', '2024-01-01'),  
(2, '{"name": "Bob", "email": "[email protected]", "address": {"city": "Los Angeles", "zip": "90001"}}', '2024-01-02');

Запити до даних JSON

1.

Доступ до полів JSON

Використовуйте функції JSON для витягування полів або навігації по вкладеним структурами.

Приклад для MySQL:

SELECT   
 JSON_EXTRACT(customer_details, '$.name') AS customer_name,  
 JSON_EXTRACT(customer_details, '$.address.city') AS city  
FROM orders;

Результат:

| customer_name | city |  
|---------------|--------------|  
| "Alice" | "New York" |  
| "Bob" | "Los Angeles"|

Приклад для PostgreSQL:

SELECT   
 customer_details->>'name' AS customer_name,  
 customer_details->'address'->>'city' AS city  
FROM orders;

2. Фільтрація за полями JSON

Фільтруйте рядки на основі значень полів JSON.

Приклад для MySQL:

SELECT *   
FROM orders  
WHERE JSON_EXTRACT(customer_details, '$.address.city') = 'New York';

Приклад для PostgreSQL:

SELECT *   
FROM orders  
WHERE customer_details->'address'->>'city' = 'New York';

3.

Агрегування даних JSON

Комбінуйте поля JSON в один об'єкт JSON.

Приклад для PostgreSQL:

SELECT   
 JSON_BUILD_OBJECT('order_id', order_id, 'customer', customer_details) AS order_summary  
FROM orders;

Результат:

| order_summary |  
|------------------------------------------------------------------------------|  
| {"order_id": 1, "customer": {"name": "Alice", "email": "[email protected]"}} |  
| {"order_id": 2, "customer": {"name": "Bob", "email": "[email protected]"}} |

4. Оновлення даних JSON

Модифікуйте конкретні ключі або значення у стовпці JSON.

Приклад для MySQL:

UPDATE orders  
SET customer_details = JSON_SET(customer_details, '$.address.zip', '12345')  
WHERE order_id = 1;

Приклад для PostgreSQL:

UPDATE orders  
SET customer_details = JSONB_SET(customer_details, '{address, zip}', '"12345"')  
WHERE order_id = 1;

5.

Розширення масивів JSON

Розбивайте масиви JSON на окремі рядки.

Приклад даних:

INSERT INTO orders (order_id, customer_details, order_date)  
VALUES  
(3, '{"items": ["item1", "item2", "item3"]}', '2024-01-03');

Приклад для PostgreSQL:

SELECT   
 order_id,   
 json_array_elements_text(customer_details->'items') AS item  
FROM orders  
WHERE order_id = 3;

Результат:

| order_id | item |  
|----------|--------|  
| 3 | item1 |  
| 3 | item2 |  
| 3 | item3 |

Кращі практики обробки JSON у SQL

1. Індексуйте поля JSON: Використовуйте функціональні або віртуальні індекси для прискорення пошуку.

Приклад для MySQL:

CREATE INDEX idx_city ON orders (JSON_EXTRACT(customer_details, '$.address.city'));

Приклад для PostgreSQL:

CREATE INDEX idx_city ON orders USING gin (customer_details->'address');

2. Перевірка JSON: Забезпечте цілісність даних, перевіряючи JSON перед вставкою.

SELECT IS_JSON('{"name": "Alice"}'); -- MySQL

3.
Поєднання JSON з реляційними даними: Використовуйте JSON для гнучких полів, а традиційні стовпці для структурованих даних.

Загальні помилки, яких слід уникати

1. Надмірне використання JSON: Не використовуйте JSON для повністю структурованих даних, які добре підходять для реляційних таблиць.
2. Ігнорування продуктивності: Запити до великих об'єктів JSON можуть бути повільними без належного індексування.
3. Складні вкладені структури: Глибоко вкладений JSON може ускладнити підтримку запитів.

Висновок

Обробка JSON у SQL заповнює прогалину між реляційними та NoSQL базами даних, дозволяючи працювати з гнучкими форматами даних, при цьому використовуючи потужність SQL. Оволодівши функціями JSON, ви зможете ефективно запитувати, оновлювати та маніпулювати напівструктурованими даними.

У наступній статті ми розглянемо SQL Pivot і Unpivot, зосередившись на трансформації рядків у стовпці і навпаки для динамічного перерозподілу даних.

Перекладено з: Working with JSON Data in SQL: A Modern Approach