JSON Functions in SQL: How to Query JSON Data
💡 Concept Name
JSON Functions in SQL – Functions that allow you to parse, extract, and manipulate JSON-formatted data directly within SQL queries.
📘 Quick Intro
Modern relational databases like SQL Server, PostgreSQL, and MySQL support JSON functions to store and query structured JSON data without needing external parsers. These functions let you navigate nested objects, extract fields, and even generate JSON from query results.
🧠 Analogy / Short Story
Imagine a library storing books in boxes (JSON objects). Instead of opening each box manually, you use labeled keys to instantly retrieve the title or author from any box. SQL JSON functions give you that efficient access without unpacking the entire structure every time.
🔧 Technical Explanation
- 📦 JSON is stored as a string in SQL columns but can be parsed using built-in functions.
- 🧾
JSON_VALUE()extracts a scalar value from JSON in SQL Server. - 📂
JSON_QUERY()returns an object or array from JSON. - 📤
FOR JSONserializes query results into JSON format. - 🔎 PostgreSQL uses operators like
->and->>to navigate JSONB fields. - 🔧 MySQL provides
JSON_EXTRACT(),JSON_UNQUOTE(), and more for similar tasks.
💻 Real Code Example
-- Sample table with JSON in SQL Server
SELECT JSON_VALUE(Profile, '$.name') AS Name,
JSON_VALUE(Profile, '$.email') AS Email
FROM Users;
-- PostgreSQL example
SELECT data->>'name' AS Name,
data->'address'->>'city' AS City
FROM Customers;
-- MySQL example
SELECT JSON_UNQUOTE(JSON_EXTRACT(profile, '$.name')) AS Name
FROM user_profiles;
❓ Interview Q&A
Q1: What is the purpose of JSON functions in SQL?
A: To extract, query, and manipulate JSON-formatted data stored in text columns.
Q2: Which function extracts a scalar value in SQL Server?
A: JSON_VALUE().
Q3: How does PostgreSQL access a field inside a JSON object?
A: Using -> for objects and ->> for scalar values.
Q4: How do you generate JSON output from a query?
A: Use FOR JSON in SQL Server or row_to_json() in PostgreSQL.
Q5: Is JSON stored as native data type in all databases?
A: No, some (like PostgreSQL) use JSONB; others (like SQL Server) treat it as string.
Q6: Can JSON columns be indexed?
A: Yes, in PostgreSQL with expression indexes; SQL Server requires computed columns.
Q7: What is the role of JSON_QUERY()?
A: To extract an object or array instead of a scalar.
Q8: Why is querying JSON useful in relational databases?
A: It allows semi-structured data to coexist with structured schema, ideal for logs, metadata, etc.
Q9: Does MySQL support native JSON functions?
A: Yes, since version 5.7 with functions like JSON_EXTRACT().
Q10: What is the difference between -> and ->> in PostgreSQL?
A: -> returns JSON; ->> returns text value (scalar).
📝 MCQs
Q1. Which function is used to extract scalar values from JSON in SQL Server?
- JSON_QUERY()
- JSON_PARSE()
- JSON_VALUE()
- GET_JSON()
Q2. Which PostgreSQL operator extracts scalar value from JSON?
- ->
- >>
- ->>
- #>
Q3. Which SQL clause generates JSON output from queries?
- TO_JSON
- FOR JSON
- AS JSON
- WITH JSON
Q4. Which MySQL function extracts value from JSON?
- JSON_FETCH()
- GET_JSON()
- JSON_EXTRACT()
- VALUE()
Q5. What does JSON_QUERY() return?
- Scalar
- Boolean
- Null
- Object or array
Q6. How is JSON data stored in SQL Server?
- As JSON type
- As XML
- As NVARCHAR
- As ARRAY
Q7. Can JSON be indexed in PostgreSQL?
- No
- Only as keys
- Yes, with expression indexes
- Only in joins
Q8. What function generates JSON in PostgreSQL?
- to_json()
- row_to_json()
- as_json()
- json_format()
Q9. What is JSONB in PostgreSQL?
- XML version
- Old JSON syntax
- Binary format of JSON
- JSON from stored proc
Q10. Why use JSON in relational databases?
- Replace primary keys
- Speed up joins
- Reduce tables
- To store semi-structured data
💡 Bonus Insight
While JSON provides flexibility, excessive use in relational databases can compromise performance and normalization. Use it when you need dynamic or nested structures, and index wisely for queries.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!