Creating JSON columns in PostgreSQL - Visual Walkthrough

Concept Flow - Creating JSON columns

Start: Define Table

↓

Specify Column Name and Type JSON or JSONB

↓

Execute CREATE TABLE Statement

↓

Table Created with JSON Column

↓

Insert JSON Data into Column

↓

Query JSON Data from Table

This flow shows how to create a table with a JSON column, insert JSON data, and query it.

Execution Sample

PostgreSQL

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

INSERT INTO users (info) VALUES ('{"name": "Alice", "age": 30}');

Creates a table with a JSONB column and inserts a JSON object into it.

Execution Table

Step	SQL Command	Action	Result
1	CREATE TABLE users (id SERIAL PRIMARY KEY, info JSONB);	Create table with JSONB column	Table 'users' created with columns 'id' and 'info' (JSONB)
2	INSERT INTO users (info) VALUES ('{"name": "Alice", "age": 30}');	Insert JSON data into 'info' column	One row inserted with JSON data in 'info'
3	SELECT * FROM users;	Query all rows	Returns rows with 'id' and JSON data in 'info' column
4	SELECT info->>'name' FROM users;	Extract 'name' from JSON	Returns 'Alice' from JSON data
5	END	No more commands	Execution complete

💡 All commands executed successfully; JSON column created and data inserted.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
Table 'users'	Does not exist	Exists with columns 'id' and 'info' (JSONB)	Same	Same	Same
Row count	0	0	1	1	1
info column data	N/A	N/A	{"name": "Alice", "age": 30}	{"name": "Alice", "age": 30}	{"name": "Alice", "age": 30}

Key Moments - 3 Insights

Why do we use JSONB instead of JSON for the column type?

JSONB stores JSON data in a binary format that is faster to query and index, as shown in Step 1 where the column is defined as JSONB.

How is JSON data inserted into the JSONB column?

In Step 2, JSON data is inserted as a string literal representing a JSON object, which PostgreSQL converts into JSONB format automatically.

How do we extract a value from the JSON column?

Step 4 shows using the ->> operator to extract the 'name' field as text from the JSONB column.

Visual Quiz - 3 Questions

Look at the execution table, what is the data type of the 'info' column after Step 1?

A TEXT

B JSONB

C VARCHAR

D INTEGER

💡 Hint

Check Step 1 in the execution table where the table is created.

At which step is JSON data inserted into the table?

A Step 2

B Step 3

C Step 1

D Step 4

💡 Hint

Look for the INSERT command in the execution table.

If we want to get the 'age' from the JSON column, which SQL operator would we use similar to Step 4?

A ||

B ::json

C ->>

D ->

💡 Hint

Step 4 uses ->> to extract 'name' as text from JSON.

Concept Snapshot

CREATE TABLE table_name (
  column_name JSON or JSONB
);

Use JSONB for efficient storage and querying.
Insert JSON data as string literals.
Use ->> operator to extract text values from JSON columns.

Full Transcript

This lesson shows how to create a table with a JSON column in PostgreSQL. First, you define the table with a column of type JSON or JSONB. JSONB is preferred because it stores data in a binary format that is faster to query. Then, you insert JSON data as a string literal into the JSONB column. Finally, you can query the table and extract values from the JSON data using the ->> operator. The execution table traces these steps with commands and results. The variable tracker shows the table and data state after each step. Key moments clarify why JSONB is used, how data is inserted, and how to extract values. The quiz tests understanding of these steps and operators.