Inserting JSON data in PostgreSQL - Step-by-Step Execution

Concept Flow - Inserting JSON data

Start

↓

Prepare JSON data

↓

Write INSERT statement with JSON

↓

Execute INSERT

↓

Data stored in JSON column

↓

End

This flow shows how JSON data is prepared and inserted into a PostgreSQL table with a JSON column.

Execution Sample

PostgreSQL

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

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

Creates a table with a JSON column and inserts a JSON object as a string into that column.

Execution Table

Step	Action	Input/Query	Result/State
1	Create table	CREATE TABLE users (id SERIAL PRIMARY KEY, info JSON);	Table 'users' created with 'info' column of type JSON
2	Prepare JSON data	{"name": "Alice", "age": 30}	JSON string ready to insert
3	Insert JSON data	INSERT INTO users (info) VALUES ('{"name": "Alice", "age": 30}');	One row inserted with JSON data in 'info' column
4	Verify insert	SELECT * FROM users;	Returns: id=1, info={"name": "Alice", "age": 30}
5	End	-	Data stored successfully, execution complete

💡 Insertion completes after one row is added with JSON data stored in the 'info' column.

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
users table	Does not exist	Exists with JSON column	Contains 1 row with JSON data	Contains 1 row with JSON data

Key Moments - 2 Insights

Why do we put the JSON data inside single quotes in the INSERT statement?

Because PostgreSQL expects JSON data as a string literal in the query, so we enclose the JSON object in single quotes to treat it as text.

Can we insert JSON data without creating a JSON column first?

No, the table must have a column of type JSON or JSONB to store JSON data properly, as shown in step 1.

Visual Quiz - 3 Questions

Look at the execution table, what is the state of the 'users' table after step 3?

A Table exists but has no rows

B Table contains 1 row with JSON data

C Table does not exist yet

D Table contains 1 row with plain text data

💡 Hint

Check the 'Result/State' column in row 3 of the execution table.

At which step do we verify that the JSON data was inserted correctly?

A Step 1

B Step 2

C Step 4

D Step 5

💡 Hint

Look for the step where a SELECT query is run to check the data.

If we forget to put single quotes around the JSON in the INSERT, what will happen?

A The query will fail with a syntax error

B The JSON will insert correctly anyway

C The JSON will be stored as plain text without quotes

D The database will automatically add quotes

💡 Hint

Recall why single quotes are needed around JSON strings in SQL queries.

Concept Snapshot

Inserting JSON data in PostgreSQL:
- Create a table with a JSON or JSONB column.
- Prepare JSON data as a string.
- Use INSERT INTO table (json_column) VALUES ('json_string');
- JSON must be enclosed in single quotes.
- Verify insertion with SELECT query.

Full Transcript

This visual execution shows how to insert JSON data into a PostgreSQL table. First, we create a table named 'users' with a column 'info' of type JSON. Then, we prepare a JSON string representing user information. Next, we insert this JSON string into the 'info' column using an INSERT statement with the JSON enclosed in single quotes. After insertion, we verify the data by selecting all rows from the table, confirming the JSON is stored correctly. The process ends with the data safely stored in the JSON column.

Practice

1. What is the correct way to insert JSON data into a PostgreSQL table with a json column named data?

easy

A. INSERT INTO table_name (data) VALUES (JSON('name: John, age: 30'));

B. INSERT INTO table_name (data) VALUES ({name: 'John', age: 30});

C. INSERT INTO table_name (data) VALUES ("{'name': 'John', 'age': 30}");

D. INSERT INTO table_name (data) VALUES ('{"name": "John", "age": 30}');

Solution

1. Step 1: Use single quotes for JSON string in SQL

   In PostgreSQL, JSON data must be enclosed in single quotes as a string literal.

2. Step 2: Use valid JSON format inside the string

   The JSON must have double quotes around keys and string values, like {"name": "John", "age": 30}.

3. Final Answer:

   INSERT INTO table_name (data) VALUES ('{"name": "John", "age": 30}'); -> Option D

4. Quick Check:

   JSON string with single quotes and double quotes inside = D [OK]

Hint: Always use single quotes around JSON string in SQL [OK]

Common Mistakes:

• Using double quotes for the whole JSON string
• Using unquoted keys or single quotes inside JSON
• Trying to insert JSON without quotes

2. Which of the following is the correct syntax to insert JSON data into a table named users with a jsonb column info?

easy

A. INSERT INTO users (info) VALUES (jsonb '{"city": "NY", "zip": 10001}');

B. INSERT INTO users (info) VALUES ('{city: "NY", zip: 10001}');

C. INSERT INTO users (info) VALUES ("{'city': 'NY', 'zip': 10001}");

D. INSERT INTO users (info) VALUES (jsonb '{city: NY, zip: 10001}');

Solution

1. Step 1: Use jsonb type cast for jsonb columns

   When inserting into a jsonb column, you can cast the string to jsonb using jsonb '...' .

2. Step 2: Use valid JSON string with double quotes inside single quotes

   The JSON must be valid with double quotes around keys and string values.

3. Final Answer:

   INSERT INTO users (info) VALUES (jsonb '{"city": "NY", "zip": 10001}'); -> Option A

4. Quick Check:

   jsonb cast with valid JSON string = C [OK]

Hint: Cast JSON string to jsonb with jsonb '...' for jsonb columns [OK]

Common Mistakes:

• Using double quotes for the whole JSON string
• Not casting to jsonb when required
• Using invalid JSON format without quotes

3. Given the table products with a json column details, what will be the result of this query?

INSERT INTO products (details) VALUES ('{"color": "red", "size": "M"}');
SELECT details->>'color' FROM products WHERE details->>'size' = 'M';

medium

A. ["M"]

B. [null]

C. ["red"]

D. Empty result

Solution

1. Step 1: Insert JSON with color and size keys

   The insert adds a row with details containing color: red and size: M.

2. Step 2: Select color where size equals 'M'

   The SELECT filters rows where details->>'size' = 'M' and returns details->>'color', which is 'red'.

3. Final Answer:

   ["red"] -> Option C

4. Quick Check:

   Filter by size 'M' returns color 'red' [OK]

Hint: Use ->> to get JSON text value for filtering and selecting [OK]

Common Mistakes:

• Using -> instead of ->> returns JSON, not text
• Expecting size value instead of color in output
• Not matching the filter condition correctly

4. You try to insert JSON data with this query:

INSERT INTO orders (info) VALUES ('{customer: "Alice", total: 50}');

But it fails. What is the error and how to fix it?

medium

A. Using double quotes inside JSON string; fix by using single quotes inside JSON.

B. Missing double quotes around JSON keys; fix by adding double quotes around keys.

C. JSON keys must be unquoted; fix by removing all quotes.

D. JSON must be cast to jsonb; fix by adding ::jsonb after string.

Solution

1. Step 1: Identify invalid JSON format

   JSON keys must be in double quotes, but here keys are unquoted (customer, total).

2. Step 2: Fix JSON by adding double quotes around keys

   Correct JSON is '{"customer": "Alice", "total": 50}'.

3. Final Answer:

   Missing double quotes around JSON keys; fix by adding double quotes around keys. -> Option B

4. Quick Check:

   JSON keys need double quotes = A [OK]

Hint: JSON keys must always be double-quoted strings [OK]

Common Mistakes:

• Using unquoted keys in JSON
• Mixing single and double quotes incorrectly
• Assuming casting fixes invalid JSON format

5. You want to insert multiple JSON objects into a table events with a jsonb column payload. Which query correctly inserts two JSON objects in one statement?

hard

A. INSERT INTO events (payload) VALUES (jsonb '{"type": "click", "x": 10}'), (jsonb '{"type": "scroll", "y": 20}');

B. INSERT INTO events (payload) VALUES '[{"type": "click", "x": 10}, {"type": "scroll", "y": 20}]';

C. INSERT INTO events (payload) VALUES (jsonb '[{"type": "click", "x": 10}, {"type": "scroll", "y": 20}]');

D. INSERT INTO events (payload) VALUES (jsonb '{type: click, x: 10}'), (jsonb '{type: scroll, y: 20}');

Solution

1. Step 1: Insert multiple rows with separate JSON objects

   Use multiple VALUES tuples, each with a valid JSON string cast to jsonb.

2. Step 2: Ensure JSON keys and strings are double-quoted

   Each JSON object must have keys and string values in double quotes inside single quotes.

3. Final Answer:

   INSERT INTO events (payload) VALUES (jsonb '{"type": "click", "x": 10}'), (jsonb '{"type": "scroll", "y": 20}'); -> Option A

4. Quick Check:

   Multiple rows with valid JSON objects = A [OK]

Hint: Use multiple VALUES tuples with jsonb cast for multiple JSON inserts [OK]

Common Mistakes:

• Trying to insert JSON array as one JSON object
• Using invalid JSON without quotes
• Not casting JSON string to jsonb when required