How do you work with JSON in SQL databases, and when should you store JSON in a column?
How do you work with JSON in SQL databases, and when should you store JSON in a column?
Modern engines store and query JSON natively. Use it for semi-structured or sparse attributes, not as a replacement for relational columns that you filter, join or constrain heavily.
| Engine | Type | Notes |
|---|---|---|
| PostgreSQL | JSONB (binary, indexable) and JSON (text copy) |
operators ->, ->>, @>, ?; GIN indexes; JSON_TABLE and the SQL/JSON query functions arrived in PostgreSQL 17 |
| MySQL | JSON |
->, ->>, JSON_EXTRACT, JSON_TABLE, multi-valued indexes (8.0.17+) |
| SQL Server | NVARCHAR with JSON_VALUE, JSON_QUERY, OPENJSON, FOR JSON |
native json type in SQL Server 2025 |
| Oracle | JSON |
JSON_VALUE, JSON_TABLE, JSON-relational duality views in 23ai |
CREATE TABLE events (id BIGSERIAL PRIMARY KEY, payload JSONB NOT NULL);
SELECT payload->>'user_id' AS user_id, payload->'device'->>'os' AS os
FROM events
WHERE payload @> '{"type": "purchase"}'; -- containment, uses a GIN index
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.