Creating JSON columns in PostgreSQL

Introduction

JSON columns let you store flexible data inside a table. This helps when data doesn't fit fixed columns.

You want to save user preferences that vary a lot.

You need to store product details that change often.

You want to keep logs or events with different fields.

You want to add extra info without changing table structure.

Syntax

PostgreSQL

CREATE TABLE table_name (
  column_name JSON
);

Use JSON type to store JSON data as text with validation.

You can also use JSONB for faster queries and indexing.

Examples

This creates a table with a JSON column named info to store user data.

PostgreSQL

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  info JSON
);

This creates a table with a JSONB column for efficient JSON storage and queries.

PostgreSQL

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  details JSONB
);

Sample Program

This example creates an employees table with a JSON column attributes. It inserts two employees with JSON data about age and skills, then selects all rows.

PostgreSQL

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT,
  attributes JSON
);

INSERT INTO employees (name, attributes) VALUES
('Alice', '{"age": 30, "skills": ["SQL", "Python"]}'),
('Bob', '{"age": 25, "skills": ["Java", "React"]}');

SELECT id, name, attributes FROM employees;

Important Notes

JSON columns store data as text but check it is valid JSON.

Use JSONB if you want faster searching inside JSON data.

Remember to use single quotes around JSON strings in SQL.

Summary

JSON columns let you store flexible, structured data inside tables.

Use CREATE TABLE with JSON or JSONB type to add JSON columns.

Insert JSON data as strings inside single quotes.