Inserting JSON data in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the correct SQL command to insert JSON data into a PostgreSQL table?

Use the INSERT INTO statement with a column of type json or jsonb. The JSON data should be provided as a string literal enclosed in single quotes.

beginner

What data types in PostgreSQL are used to store JSON data?

PostgreSQL provides two data types for JSON: json which stores JSON as text, and jsonb which stores JSON in a binary format for faster processing.

beginner

How do you insert a JSON object into a table column of type jsonb?

You insert JSON data as a string literal. For example:
INSERT INTO table_name (json_column) VALUES ('{"key": "value"}');

beginner

Can you insert JSON arrays into PostgreSQL JSON columns?

Yes, you can insert JSON arrays as string literals just like JSON objects. For example:
INSERT INTO table_name (json_column) VALUES ('[1, 2, 3]');

beginner

What happens if you try to insert invalid JSON into a JSON or JSONB column?

PostgreSQL will return an error because the data must be valid JSON format to be stored in json or jsonb columns.

Which PostgreSQL data type is optimized for faster JSON processing?

A jsonb

B json

C text

D varchar

The jsonb type stores JSON in a binary format, making it faster for processing than json which stores JSON as plain text.

How should JSON data be formatted when inserting into a PostgreSQL JSON column?

A As a number

B As a string literal enclosed in single quotes

C As a plain JSON object without quotes

D As a SQL function

JSON data must be inserted as a string literal enclosed in single quotes, for example: '{"key": "value"}'.

What will happen if you insert invalid JSON text into a JSON column?

A PostgreSQL will convert it automatically

B PostgreSQL will accept it as text

C PostgreSQL will throw an error

D PostgreSQL will ignore the invalid parts

PostgreSQL requires valid JSON format for JSON columns and will throw an error if the data is invalid.

Which SQL command is used to add JSON data to a table?

A INSERT INTO

B SELECT

C UPDATE

D DELETE

The INSERT INTO command is used to add new rows, including JSON data, into a table.

Can you insert a JSON array into a PostgreSQL JSON column?

A Only with special functions

B No, only JSON objects are allowed

C Only if converted to text first

D Yes, as a string literal

JSON arrays can be inserted as string literals just like JSON objects.

Explain how to insert JSON data into a PostgreSQL table column.

Think about the SQL command and how JSON is represented as text.

What are the differences between json and jsonb data types in PostgreSQL?

Consider storage format and performance.

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