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.

A Real Business Scenario: Storing Webhook Payloads Without Freezing the Schema

JSON becomes useful when a system needs to accept changing payloads without redesigning the table every week.

Imagine you are storing:

• payment provider webhook events with vendor-specific fields
• product attributes that differ by category
• user preferences where some features exist only for certain accounts
• audit payloads that should preserve the original request shape

In all of those cases, a pure relational model can become awkward too early. JSON lets you keep the core entity relational while preserving semi-structured details that are still evolving.

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';

A Common Mistake: Hiding Core Business Fields Inside JSON Forever

Teams often start with a sensible JSON column for flexibility and then quietly let it absorb more and more important fields.

That turns into problems like:

• critical identifiers living in JSON paths instead of typed columns
• repeated json_extract(...) expressions in every report
• missing constraints on values the business actually depends on
• harder indexing and slower filters on frequently queried attributes

The useful rule is simple: JSON is good for flexible edge fields. It is a poor long-term home for stable join keys, required business facts, or frequently filtered metrics.

When JSON Is the Right Modeling Choice

JSON works best when the data is:

• optional or highly variable across rows
• useful to keep together as one nested payload
• not the primary join key of the system
• queried occasionally rather than in every core report

That is why JSON often fits:

• product attributes that differ by category
• raw webhook or API payload snapshots
• event properties that are useful for debugging
• feature flags or settings blobs with uneven shape

The healthy pattern is usually:

• keep the core relational fields explicit
• keep the flexible edge fields in JSON

That gives you both structure and adaptability without forcing every attribute into a first-class column too early.

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.

A Practical Workflow for JSON-in-SQL Decisions

When a team is unsure whether a field should become a real column or stay in JSON, use this sequence:

1. Identify which attributes are required for every row.
2. Promote join keys, foreign keys, and frequently filtered fields to normal columns.
3. Keep sparse or fast-changing attributes in JSON first.
4. Watch which JSON keys repeatedly show up in filters and reports.
5. Promote those repeated keys into relational columns when they become stable.

This keeps the schema from becoming prematurely rigid while avoiding the opposite mistake of hiding half the data model inside one opaque blob.

Boundary and Performance Notes

JSON support makes relational databases more flexible, but it does not erase tradeoffs:

• extracting keys on the fly is usually slower than reading typed columns
• nested paths are harder to validate than schema-level constraints
• dialect behavior differs across SQLite, PostgreSQL, and MySQL
• indexing JSON expressions is possible, but it is more complex than indexing a normal column

That is why strong systems usually keep a narrow relational spine and let JSON handle the optional or changing edges.

When NOT to Reach for JSON

Avoid defaulting to JSON when:

• the field participates in foreign keys or core joins
• the value is required on almost every row
• analysts will filter, group, or sort on it constantly
• the schema is already stable enough to model explicitly

In those cases, plain columns are easier to validate, index, and reason about.

Official References

• SQLite JSON functions documentation for json_extract, json_set, and the SQLite JSON extension.
• PostgreSQL JSON functions and operators for ->, ->>, jsonb, and path querying in PostgreSQL.
• MySQL JSON functions for dialect differences when production is not SQLite.

FAQ

Should I store all optional attributes in one JSON column?

Not automatically. A JSON column is useful when the attributes are genuinely sparse or unstable. If the same keys appear in most rows and drive real filtering or reporting, they probably want to become normal columns.

Is JSON in SQL a replacement for good schema design?

No. JSON is a supplement to relational modeling, not a replacement for it. Core entities, identifiers, constraints, and relationships still belong in the relational part of the schema.

Can JSON make queries slower?

Yes. Extracting keys on the fly is usually more expensive than reading typed columns, especially when filters, grouping, or sorting happen often.

Related Articles

• Designing Your First Database Schema for the broader decision process around what belongs in tables and what does not.
• Database Normalization Explained for the structured side of removing redundancy before reaching for semi-structured fields.
• SQL Table Relationships: One-to-Many and Many-to-Many for the foreign-key patterns that JSON should not quietly replace.

Conclusion

JSON in SQL is useful when it stays in its lane. Keep the relational core explicit, use JSON for sparse or evolving attributes, and promote repeated keys into first-class columns once they become part of the real data model.