Can you store JSON in SQL Server and PostgreSQL?

Yes. SQL Server stores JSON in NVARCHAR fields using functions like JSON_VALUE, while PostgreSQL supports native JSON and JSONB types.

Working with JSON Data in SQL Server / PostgreSQL | FullStackPrep.Dev

Q: What is the difference between JSON and JSONB in PostgreSQL?

JSONB is a binary representation of JSON that is faster and allows indexing, making it better for performance-sensitive applications.

💡 Concept Name

JSON in SQL enables flexible storage and querying of structured, hierarchical data within relational tables using native JSON functions.

📘 Quick Intro

SQL Server and PostgreSQL both support JSON data, allowing developers to work with semi-structured data inside columns. PostgreSQL supports rich operations via JSON and JSONB types, while SQL Server offers built-in functions like OPENJSON and JSON_VALUE().

🧠 Analogy / Short Story

Think of a traditional SQL table as a filing cabinet with fixed folders (columns). JSON is like adding envelopes inside the folders with additional flexible notes (key-value pairs). You can open and read these notes when needed, without altering the cabinet structure. JSON in SQL gives you the flexibility of NoSQL with the power of structured queries.

🔧 Technical Explanation

📦 SQL Server uses NVARCHAR columns to store JSON strings.
🛠️ Use JSON_VALUE(), OPENJSON(), and ISJSON() to parse and validate JSON.
🐘 PostgreSQL supports JSON and JSONB types natively with operators like -> and ->>.
📝 PostgreSQL functions include jsonb_extract_path(), jsonb_array_elements(), etc.
⚡ JSONB is faster and indexable; use it for performance in PostgreSQL.

🎯 Purpose & Use Case

✅ Store flexible metadata or dynamic attributes per record.
✅ Process hierarchical APIs or nested objects inside SQL queries.
✅ Avoid schema changes when dealing with frequently evolving data models.
✅ Enhance hybrid SQL-NoSQL applications while preserving ACID compliance.

💻 Real Code Example

-- SQL Server: Parse JSON field
SELECT
  JSON_VALUE(Data, '$.name') AS Name,
  JSON_VALUE(Data, '$.age') AS Age
FROM Customers
WHERE ISJSON(Data) = 1;

-- PostgreSQL: Access JSONB field
SELECT
  Data->>'name' AS Name,
  Data->'address'->>'city' AS City
FROM Customers;

-- PostgreSQL: Expand array of JSON objects
SELECT *
FROM jsonb_array_elements('[{"x":1}, {"x":2}]'::jsonb);

❓ Interview Q&A

Q1: Does SQL Server have a native JSON type?
A: No. It stores JSON as NVARCHAR and uses functions to manipulate it.

Q2: What’s the difference between JSON and JSONB in PostgreSQL?
A: JSONB is binary format, faster for indexing and comparisons.

Q3: How can you extract a value from JSON in PostgreSQL?
A: Use ->> to get a text value or -> for a JSON object.

Q4: What is OPENJSON in SQL Server?
A: A table-valued function that converts JSON arrays or objects into rows.

Q5: Can JSON columns be indexed?
A: Yes in PostgreSQL (with JSONB + GIN), not natively in SQL Server.

Q6: Why use JSON in relational databases?
A: To store flexible or semi-structured data without schema changes.

Q7: How to validate JSON in SQL Server?
A: Use ISJSON() to check if a string is valid JSON.

Q8: Is JSONB case-sensitive?
A: Yes, both JSON and JSONB are case-sensitive in PostgreSQL.

Q9: Which PostgreSQL operator accesses nested values?
A: Use -> for object navigation and ->> for scalar extraction.

Q10: How to extract from JSON arrays?
A: Use json_array_elements() or OPENJSON() depending on DBMS.

📝 MCQs

Q1. What type stores JSON in SQL Server?

JSON
TEXT
XML
NVARCHAR

Q2. Which PostgreSQL type allows indexing?

TEXT
JSON
JSONB
VARCHAR

Q3. What does JSON_VALUE() return?

Rowset
Entire JSON
Scalar value from JSON
XML output

Q4. Which function expands JSON arrays in PostgreSQL?

json_unnest()
jsonb_elements()
jsonb_array_elements()
json_split()

Q5. What operator extracts scalar text in PostgreSQL?

->
->>
->text()
::json

Q6. Which clause parses JSON into rows in SQL Server?

JSON_TABLE
ISJSON
OPENJSON
FLATTEN

Q7. How do you check for valid JSON in SQL Server?

ISJSON()
VALIDATE_JSON()
CHECK_JSON()
JSON_VALID()

Q8. Which format is faster: JSON or JSONB?

JSON
JSONB
TEXT
VARCHAR

Q9. Is JSON indexed by default in SQL Server?

Yes
No
Only arrays
Only scalar

Q10. What is the purpose of storing JSON in SQL?

Avoid joins
Optimize foreign keys
Store flexible schema data
Enhance encryption

💡 Bonus Insight

Storing JSON can reduce the need for frequent schema changes, especially in microservices or loosely coupled systems. However, overuse of JSON fields can reduce relational integrity, so it’s best for optional or non-critical attributes. In PostgreSQL, prefer JSONB for indexing and performance. Always validate and sanitize JSON input before storage.

📄 PDF Download

Need a handy summary for your notes? Download this topic as a PDF!

📄 PDF Download Coming Soon

Working with JSON Data in SQL Server / PostgreSQL