Or, how to store messy data and still feel like a database wizard!
Why JSON & XML in a Relational Database?
Once upon a time, relational databases were simple:
- Tables with neat rows & columns.
- Structured data, perfectly organized.
- Life was good.
Then the world said: "Nah, let's store random, unpredictable, messy data!"
Enter JSON & XML—the wild west of data formats.
- JSON is the cool, modern kid used by APIs, JavaScript, and NoSQL.
- XML is the grandpa that corporations refuse to let die.
And guess what? PostgreSQL can handle BOTH like a champ!
Storing JSON in PostgreSQL: More Power Than Your Average NoSQL
PostgreSQL Has TWO JSON Types:
| JSON Type | Description |
|---|---|
| JSON | Just stores text (not indexed, no fancy functions). |
| JSONB | Stores JSON in binary format (faster, indexable, and your best friend). |
Which One Should You Use?
Use JSONB → if you need fast queries and indexing.
Use JSON → if you just want to store text and don't care about performance.
Creating a Table with JSON Data
Let's say we need to store user profiles, but people can have random extra data (like favorite pizza toppings, spaceship preferences, etc.).
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
data JSONB -- Flexible, because we never know what users will store!
);
Inserting JSON Data
INSERT INTO users (name, data)
VALUES ('Alice', '{"age": 25, "hobbies": ["gaming", "coding"]}');
Notice: That’s valid JSON inside a string!
Querying JSON: Extracting Data
Get a JSON Field Value
SELECT name, data->>'age' AS age FROM users;
Result:
| name | age |
|---|---|
| Alice | 25 |
The ->> operator extracts a text value from JSONB.
Filter Users Based on JSON Data
Want to find all users who have "gaming" as a hobby?
SELECT * FROM users WHERE data @> '{"hobbies": ["gaming"]}';
BOOM! JSONB magic!
Updating JSON Fields
Oops, Alice just had a birthday! Let’s update her age:
UPDATE users
SET data = jsonb_set(data, '{age}', '26')
WHERE name = 'Alice';
jsonb_set() lets you modify only part of the JSON, instead of replacing the whole thing.
Indexing JSON for Speed
JSON queries can be slow, but PostgreSQL has GIN indexes for that!
CREATE INDEX idx_users_data ON users USING gin (data);
Now, queries like are lightning fast!
XML in PostgreSQL: Because Enterprises Still Love It
While JSON is the new hotness, XML is still used in legacy systems (cough banks, government, telecom cough).
PostgreSQL supports XML natively, so you can store, query, and validate XML just like JSON.
Storing XML Data
CREATE TABLE books (
id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
details XML -- Because book metadata is a messy XML nightmare.
);
INSERT INTO books (title, details)
VALUES ('The Hobbit', '<book><author>J.R.R. Tolkien</author><year>1937</year></book>');
Querying XML Data
PostgreSQL provides XPath support (because, why not?).
Find all books written in 1937:
SELECT title FROM books
WHERE xpath('//year/text()', details) = '{1937}';
Yes, XML queries are weird.
Converting XML to JSON (Because We’re Modern Now!)
SELECT xpath('//author/text()', details)::TEXT AS author FROM books;
JSON vs. XML: The Ultimate Showdown
| Feature | JSONB | XML |
|---|---|---|
| Human Readable? | Yes | No |
| Fast Querying? | Yes | Slow |
| Supports Hierarchical Data? | Yes | Yes |
| Indexing? | GIN Index | Not Efficient |
| Used in APIs? | 99% | No |
| Used in Legacy Systems? | No | Yes |
Use JSONB → If you want fast, flexible, modern data storage.
Use XML → If your enterprise overlords force you to.
Converting Between JSON and XML (Yes, You Can Do That!)
Need to turn JSON into XML? PostgreSQL can handle it!
SELECT xmlforest(data) FROM users;
Want to convert XML to JSON?
SELECT json_agg(xpath('//author/text()', details)) FROM books;
PostgreSQL: The Swiss Army Knife of Data Formats!
JSON Wins, But XML Refuses to Die
JSONB is the king for modern applications—fast, flexible, and highly indexable.
XML is still here for legacy systems and enterprise nightmares.
PostgreSQL handles both like a pro—because you shouldn’t have to choose.
Store messy data, and be a PostgreSQL legend!
0 Comments