Why Inserting JSON data in PostgreSQL? - Purpose & Use Cases

The Big Idea

What if you could store messy, different data easily without losing any detail or wasting time?

The Scenario

Imagine you have a big box of paper forms filled out by customers, each with different details. You want to store all these forms in your computer to find and use the information later.

But the forms are messy and don't follow the same pattern, so typing each detail into separate columns in a spreadsheet feels impossible.

The Problem

Trying to fit all these different forms into fixed columns means you waste time creating many empty fields or miss important details.

Manually typing or copying each form's data into a strict table is slow and full of mistakes.

The Solution

Inserting JSON data lets you store all the messy, different details in one flexible column that understands the structure of your forms.

This way, you keep all the information together, easy to search and update, without forcing it into a rigid format.

Before vs After

✗ Before

INSERT INTO customers (name, age, address) VALUES ('Alice', 30, '123 Main St');

✓ After

INSERT INTO customers (data) VALUES ('{"name": "Alice", "age": 30, "address": "123 Main St"}'::jsonb);

What It Enables

You can now store and query complex, varied data easily, making your database smarter and more adaptable.

Real Life Example

A company collects customer feedback forms where each person writes different comments and ratings. Using JSON, they store all feedback in one column, making it easy to analyze trends without losing any details.

Key Takeaways

Manual entry into fixed columns is slow and error-prone.

JSON data lets you store flexible, structured information in one place.

This makes your database easier to manage and more powerful.

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