Working with JSON in SQL: NoSQL Powers in a Relational World

For years, the war raged: SQL vs. NoSQL.

• SQL gives you structure, consistency, and relationships.
• NoSQL gives you flexibility and schema-less data.

But recently, the war ended quietly. SQL won. Why? Because modern SQL databases (PostgreSQL, SQLite, MySQL) now support JSON data types natively. You can have your strict schema and your flexible data blobs too.

Why Store JSON in SQL?

Imagine you're building an e-commerce store.

• Product A has size and color.
• Product B has voltage, wattage, and warranty.

Creating distinct columns for every possible attribute (column_voltage, column_size...) is a nightmare. Instead, you can have a metadata column that stores a JSON object:

{ "voltage": "220V", "wattage": "60W" }

Extracting Data from JSON (SQLite Focus)

Since SQL Boy runs on SQLite, we'll focus on SQLite's JSON functions, which are surprisingly powerful. The main workhorse is json_extract.

Syntax:

json_extract(column_name, '$.key_name')

• $ represents the root of the JSON object.
• .key_name is the path to the value you want.

Interactive Demo: Filtering by JSON Properties

Let's create a products table where the generic data lives in a JSON column called attributes. We'll then query products based on deep properties.

Note for PostgreSQL Users: Postgres is even more famous for JSON. It uses the ->> operator:

SELECT attributes->>'color' FROM products;

Modifying JSON

You don't need to replace the whole string to change one value. You can use json_patch or json_set.

-- Updating the size of the T-Shirt
UPDATE products_json_1
SET attributes = json_set(attributes, '$.size', 'XL')
WHERE name = 'T-Shirt';

When NOT to use JSON

Just because you can doesn't mean you should.

Don't use JSON for:

1. Foreign Keys: If you're storing {"user_id": 5} inside a JSON blob, you lose foreign key constraints. The database can't prevent you from deleting User 5.
2. Frequent Aggregation: GROUP BY json_extract(...) is much slower than grouping by a native column.
3. Search Heavy fields: While you can index JSON keys, it's more complex than indexing a regular column.

Conclusion

JSON in SQL is the perfect middle ground. Use standard columns for the core data (IDs, foreign keys, created_at dates) and JSON columns for the flexible, messy reality of the real world.