SQLite for beginners JSON
Description
Enjoy the videos and music you love, upload original content, and share it all with friends, family, and the world on YouTube.
My Notes
SQLite JSON Support - Video Notes
Introduction to SQLite JSON Support 00:00
SQLite has added support for handling JSON inside the database in recent years. While SQLite doesn't have as much JSON support as alternatives like PostgreSQL, it does allow you to store JSON in text columns and provides JSON-related functions and operators for manipulation and querying.
Why Use JSON in Databases? 00:29
Important caveat: JSON should be used sparingly in databases because:
- You lose certain features like foreign keys and other constraints
- Manipulating and querying JSON is often slow
- Queries are usually more complicated than conventional equivalents
Common Use Cases for JSON in Databases 01:00
1. Logging External API Calls 01:03
Most APIs return JSON, so it makes sense to log the JSON response as returned from the API.
2. Storing Sparsely Used Attributes 01:16
JSON is useful for storing attributes that are used infrequently. For example, in a kitchen cabinet sales system where each order is highly customized with unique build attributes.
Example: Kitchen Cabinet Order System 01:23
The example uses an order_lines table with kitchen cabinet sales information. Each row stores a cabinet with its unique set of build attributes in an attributes column (text column storing JSON).
Sample data structure:
| id | product | quantity | attributes |
|---|---|---|---|
| 1 | Cabinet | 2 | {"depth": 24, "width": 36, "height": 84, "hardware": "brass"} |
CREATE TABLE order_lines (
id integer PRIMARY KEY,
-- ...
) ;
Problem with Current Structure 02:30
In the example, every row has depth, width, and height attributes, which aren't sparsely used. Rule of thumb: Only store JSON attributes if they're sparsely used.
Querying JSON Values 03:02
To query for total quantity of 24-inch deep cabinets:
Method 1: Using JSON_EXTRACT function
SELECT SUM(quantity)
FROM order_lines
WHERE JSON_EXTRACT(attributes, '$.depth') = 24;
Method 2: Using double arrow operator
SELECT SUM(quantity)
FROM order_lines
WHERE attributes ->> '$.depth' = 24;
Both queries take 7-8 seconds to execute.
Optimizing the Table Structure 04:07
Since depth, width, and height are used frequently, they should be moved to their own columns:
1. Add new columns
ALTER TABLE order_lines ADD COLUMN depth INTEGER;
ALTER TABLE order_lines ADD COLUMN width INTEGER;
ALTER TABLE order_lines ADD COLUMN height INTEGER;
2. Populate new columns from JSON
UPDATE order_lines
SET depth = JSON_EXTRACT(attributes, '$.depth');
UPDATE order_lines
SET width = JSON_EXTRACT(attributes, '$.width');
UPDATE order_lines
SET height = JSON_EXTRACT(attributes, '$.height');
3. Remove dimensions from JSON column
UPDATE order_lines
SET attributes = JSON_REMOVE(attributes, '$.depth', '$.width', '$.height');
Performance Improvement 05:02
After moving dimensions to dedicated columns, the same query now takes 1-2 seconds instead of 7-8 seconds:
SELECT SUM(quantity)
FROM order_lines
WHERE depth = 24;
Why it's faster:
- Less input/output involved
- Less CPU used to extract values
- No JSON parsing required for the WHERE clause
Indexing JSON Expressions 05:33
For JSON attributes that must remain in JSON format, you can create indexes on JSON expressions:
CREATE INDEX idx_hardware
ON order_lines(JSON_EXTRACT(attributes, '$.hardware'));
Query example:
SELECT COUNT(*)
FROM order_lines
WHERE JSON_EXTRACT(attributes, '$.hardware') = 'brass';
This greatly improves performance for JSON-based queries.
Key Takeaways 06:05
- Use JSON in SQLite columns sparingly
- Only use JSON for sparsely used attributes
- JSON usage means losing native database features like constraints
- JSON queries are generally slower and more complicated
- Consider JSON as one of the last tools to reach for
- When you must use JSON, consider indexing frequently queried JSON expressions