JSON with Databases
Databases and JSON work closely together in modern applications. Some databases store data directly as JSON documents. Others store structured rows and columns but support JSON as a field type. Knowing how JSON fits into database workflows helps you design better applications.
Two Types of Databases That Use JSON
NoSQL Databases (Document Stores)
These databases store entire JSON objects as documents. No fixed table structure is required. Each document can have a different shape. Examples include MongoDB, CouchDB, and Firebase Firestore.
Relational Databases with JSON Support
Traditional databases like PostgreSQL and MySQL have added JSON column types. You can store a JSON object inside a single column of a regular table.
Diagram: Relational vs Document Database Storage
RELATIONAL DATABASE (MySQL)
---------------------------
Table: users
| id | name | email | city |
|----|----------|--------------------|-----------|
| 1 | Ananya | ananya@example.com | Delhi |
| 2 | Rohan | rohan@example.com | Mumbai |
DOCUMENT DATABASE (MongoDB)
---------------------------
Collection: users
Document 1:
{
"_id": "abc123",
"name": "Ananya",
"email": "ananya@example.com",
"address": { "city": "Delhi", "pin": "110001" },
"orders": [{ "id": 1, "item": "Book" }]
}
The relational table is flat. The MongoDB document is nested and flexible — one document holds all related data.
MongoDB: A JSON-Based Database
MongoDB stores data as BSON (Binary JSON), which is a binary version of JSON. When you read or write data, it looks and behaves exactly like JSON. MongoDB is one of the most popular databases for applications that work heavily with JSON APIs.
Connecting to MongoDB with Node.js
const { MongoClient } = require('mongodb');
const uri = 'mongodb://localhost:27017';
const client = new MongoClient(uri);
async function connect() {
await client.connect();
const db = client.db('shopDB');
return db;
}
Inserting a JSON Document into MongoDB
async function addProduct() {
const db = await connect();
const products = db.collection('products');
const product = {
name: "Bluetooth Speaker",
brand: "SoundMax",
price: 1299,
inStock: true,
tags: ["electronics", "audio", "portable"],
specs: {
battery: "10 hours",
bluetooth: "5.0",
weight: "350g"
}
};
const result = await products.insertOne(product);
console.log("Product added with ID:", result.insertedId);
}
Finding Documents in MongoDB
async function findProducts() {
const db = await connect();
const products = db.collection('products');
// Find all products in stock under 2000 rupees
const results = await products.find({
inStock: true,
price: { $lt: 2000 }
}).toArray();
results.forEach(p => {
console.log(p.name, p.price);
});
}
Updating a Document in MongoDB
async function updatePrice() {
const db = await connect();
const products = db.collection('products');
await products.updateOne(
{ name: "Bluetooth Speaker" }, // Filter
{ $set: { price: 999, inStock: false } } // Update
);
console.log("Price updated");
}
Deleting a Document in MongoDB
async function deleteProduct() {
const db = await connect();
const products = db.collection('products');
await products.deleteOne({ name: "Bluetooth Speaker" });
console.log("Product removed");
}
PostgreSQL: JSON Columns in a Relational Database
PostgreSQL supports two JSON column types: json and jsonb. The jsonb type stores JSON in a binary format that supports indexing and faster queries — always prefer jsonb.
Creating a Table with a JSON Column
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_name TEXT NOT NULL,
order_date DATE DEFAULT CURRENT_DATE,
items JSONB NOT NULL,
metadata JSONB
);
Inserting JSON Data into PostgreSQL
INSERT INTO orders (customer_name, items, metadata)
VALUES (
'Priya Mehta',
'[{"product": "Notebook", "qty": 3, "price": 50}, {"product": "Pen", "qty": 10, "price": 10}]',
'{"source": "website", "promoCode": "SAVE10"}'
);
Querying JSON Fields in PostgreSQL
-- Get the source from the metadata field
SELECT customer_name, metadata->>'source' AS order_source
FROM orders;
-- Filter orders that contain a specific product
SELECT * FROM orders
WHERE items @> '[{"product": "Notebook"}]';
-- Get the first item's product name
SELECT items->0->>'product' AS first_item
FROM orders;
PostgreSQL JSON Operators
Operator Meaning
-------- -------
-> Returns JSON value as JSON
->> Returns JSON value as text
@> Contains (checks if JSON contains another JSON)
? Key exists check
#> Navigate nested path, returns JSON
#>> Navigate nested path, returns text
Example:
metadata->>'source' Returns "website" as text
metadata->'promoCode' Returns "SAVE10" as JSON
items->0->>'product' Returns "Notebook" from first array item
MySQL: JSON Column Support
MySQL 5.7 and later supports a native JSON column type with functions for querying JSON data.
Creating a Table with JSON Column in MySQL
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
profile JSON
);
Inserting JSON Data in MySQL
INSERT INTO users (name, profile)
VALUES (
'Kiran Desai',
'{"city": "Hyderabad", "skills": ["Python", "SQL"], "experience": 5}'
);
Querying JSON in MySQL
-- Get city from profile
SELECT name, JSON_UNQUOTE(JSON_EXTRACT(profile, '$.city')) AS city
FROM users;
-- Shorter syntax using ->> operator (MySQL 5.7.13+)
SELECT name, profile->>'$.city' AS city
FROM users;
-- Filter by JSON value
SELECT * FROM users
WHERE profile->>'$.experience' > 3;
Firebase Firestore: JSON as the Native Format
Firebase Firestore is a cloud document database. All data is stored as JSON-like documents organized into collections. It is popular for mobile and web apps because it syncs data in real time.
Adding a Document to Firestore (JavaScript)
import { getFirestore, collection, addDoc } from 'firebase/firestore';
const db = getFirestore();
await addDoc(collection(db, 'products'), {
name: 'Wireless Earbuds',
price: 1499,
inStock: true,
createdAt: new Date()
});
Reading Documents from Firestore
import { getDocs, query, where } from 'firebase/firestore';
const q = query(collection(db, 'products'), where('inStock', '==', true));
const snapshot = await getDocs(q);
snapshot.forEach(doc => {
console.log(doc.id, doc.data());
});
Choosing the Right Database for JSON
Scenario Best Choice
-------- -----------
Flexible document structure MongoDB, Firestore
Complex relationships and joins PostgreSQL (with jsonb)
Mobile/real-time sync Firebase Firestore
Existing MySQL setup + JSON fields MySQL JSON columns
Simple config or small datasets JSON files
High-speed structured data PostgreSQL relational tables
JSON as API Bridge Between Application and Database
Browser / App
|
| (JSON over HTTP)
v
REST API Server
|
| (Database queries)
v
Database
|
| (Query results)
v
REST API Server
|
| (JSON response)
v
Browser / App
JSON acts as the universal language throughout this flow. The browser sends JSON, the API receives JSON, the database stores it (in some form), and the API returns JSON to the browser. This consistency makes modern application development fast and predictable.
Summary
JSON and databases work together in two main ways: document databases like MongoDB store JSON natively as documents, while relational databases like PostgreSQL and MySQL support JSON column types for flexible data. Firestore uses JSON-like documents with real-time sync for mobile apps. Choose your database based on whether your data has a fixed structure (relational) or a flexible, document-like structure (NoSQL). JSON acts as the connecting language between your application, API, and database throughout the entire stack.