Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

SQL: Use JSON Functions for Modern Data

- 09.07.26 - ErcanOPAK

📋 JSON in SQL = Modern Data

JSON is everywhere. SQL JSON functions work with JSON data. Store, query, modify — flexible data.

📝 JSON Functions

# Store JSON
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    metadata JSON
);

INSERT INTO users (id, name, metadata)
VALUES (
    1,
    'Alice',
    '{"age": 30, "city": "NYC", "preferences": {"theme": "dark"}}'
);

# Query JSON
-- Extract value
SELECT JSON_EXTRACT(metadata, '$.age') as age
FROM users;

-- Or with -> operator
SELECT metadata->>'$.age' as age
FROM users;

# JSON functions
SELECT 
    JSON_TYPE(metadata) as type,
    JSON_KEYS(metadata) as keys,
    JSON_LENGTH(metadata) as length
FROM users;

# Update JSON
UPDATE users
SET metadata = JSON_SET(metadata, '$.city', 'LA')
WHERE id = 1;

# Add to JSON
UPDATE users
SET metadata = JSON_INSERT(metadata, '$.country', 'USA');

# Remove from JSON
UPDATE users
SET metadata = JSON_REMOVE(metadata, '$.preferences.theme');

# Search JSON
SELECT * FROM users
WHERE JSON_CONTAINS(metadata, '"NYC"', '$.city');

🎯 Advanced JSON Usage

# JSON array
INSERT INTO orders (id, items)
VALUES (
    1,
    '[
        {"product": "Laptop", "qty": 1, "price": 999},
        {"product": "Mouse", "qty": 2, "price": 25}
    ]'
);

# Query array
SELECT JSON_EXTRACT(items, '$[0].product') as first_product
FROM orders;

# Array functions
SELECT 
    JSON_ARRAY_LENGTH(items) as item_count,
    JSON_EXTRACT(items, '$[*].product') as products
FROM orders;

# JSON_TABLE (SQL Server)
SELECT *
FROM orders,
JSON_TABLE(items, '$[*]' COLUMNS(
    product VARCHAR(100) PATH '$.product',
    qty INT PATH '$.qty',
    price DECIMAL(10,2) PATH '$.price'
)) AS jt;

# JSON_OBJECT (MySQL)
SELECT 
    JSON_OBJECT(
        'id', id,
        'name', name,
        'age', JSON_EXTRACT(metadata, '$.age')
    ) as user_json
FROM users;

# JSON_ARRAY
SELECT JSON_ARRAY(name, metadata) as user_data
FROM users;

# Index JSON (MySQL 8.0+)
ALTER TABLE users 
ADD INDEX idx_city ((CAST(metadata->>'$.city' AS CHAR(50))));

💡 JSON Tips

  • Use JSON for flexible schemas
  • Index JSON fields for performance
  • Validate JSON before storing
  • Use JSON with NoSQL patterns
  • Consider size limits for JSON

“JSON in SQL enables modern data patterns. Flexible, powerful. Essential for modern databases.”

— Database Architect

Related posts:

SQL Index Fragmentation — What Actually Matters (and What Doesn't)

SQL Index Fragmentation — What Actually Matters (and What Doesn't)

SQL COUNT(*) Slows Down Reports

SQL COUNT(*) Slows Down Reports

SQL Server: Fix a Query Returning Different Row Counts on Reruns Because It Reads a Table Variable P...

SQL Server: Fix a Query Returning Different Row Counts on Reruns Because It Reads a Table Variable P...

Views: 10

Post navigation

.NET Core: Use Options Pattern for Configuration
C#: Use Default Interface Methods for API Evolution

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (857)
  • How to select distinct rows in a datatable in C# (854)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (653)
  • Add Constraint to SQL Table to ensure email contains @ (609)
  • Average of all values in a column that are not zero in SQL (570)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (547)
  • Find numbers with more than two decimal places in SQL (475)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit
  • SQL Server: Fix ROW_NUMBER Ties Reordering Between Identical Runs
  • C#: Fix an Enum HasFlag Returning True for the Zero Value
  • WordPress: Fix Featured Images Vanishing After a CDN Migration
  • Photoshop: Fix Content-Aware Fill Smearing a Repeating Pattern
  • Asp.Net Core: Fix Antiforgery Tokens Rejected Behind a Reverse Proxy

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (857)
  • How to select distinct rows in a datatable in C# (854)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com