Inserting JSON data in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this JSON insertion query?

Consider the table users with a data column of type jsonb. What will be the result after running this insertion?

INSERT INTO users (data) VALUES ('{"name": "Alice", "age": 30}');
SELECT data->>'name' AS name FROM users WHERE data->>'age' = '30';

PostgreSQL

INSERT INTO users (data) VALUES ('{"name": "Alice", "age": 30}');
SELECT data->>'name' AS name FROM users WHERE data->>'age' = '30';

A [{"name": "Alice"}]

B SyntaxError

C [{"name": null}]

D []

💡 Hint

Remember that JSON numbers are stored as numbers, but the query compares the age as a string.

The insertion stores age as a number. The WHERE clause compares the age as a string '30', which works because the operator ->> returns text. So the row matches and the name 'Alice' is returned.

📝 Syntax

intermediate

Which INSERT statement correctly inserts JSON data into a jsonb column?

Given a table products with a details column of type jsonb, which of the following INSERT statements is syntactically correct?

A INSERT INTO products (details) VALUES ('{"color": "red", "size": "M"}');

B INSERT INTO products (details) VALUES ({color: 'red', size: 'M'});

C INSERT INTO products (details) VALUES (jsonb '{"color": "red", "size": "M"}');

D INSERT INTO products (details) VALUES ("{'color': 'red', 'size': 'M'}");

💡 Hint

JSON data must be a valid JSON string literal inside single quotes.

Option A uses a valid JSON string inside single quotes. Option A is invalid syntax because JSON keys and strings must be quoted. Option A is invalid because jsonb is a type cast and requires ::jsonb or a function. Option A uses double quotes incorrectly and single quotes inside JSON keys are invalid.

❓ optimization

advanced

Which approach is best to insert multiple JSON objects efficiently?

You want to insert multiple JSON objects into a jsonb column in one query. Which option is the most efficient and syntactically correct?

A INSERT INTO logs (entry) SELECT * FROM jsonb_array_elements('[{"event": "login"}, {"event": "logout"}]');

B INSERT INTO logs (entry) VALUES jsonb_array_elements('[{"event": "login"}, {"event": "logout"}]');

C INSERT INTO logs (entry) VALUES ('{"event": "login"}'); INSERT INTO logs (entry) VALUES ('{"event": "logout"}');

D INSERT INTO logs (entry) VALUES ('{"event": "login"}'), ('{"event": "logout"}');

💡 Hint

Look for a single INSERT statement that inserts multiple rows.

Option D inserts multiple rows in one statement, which is efficient. Option D is invalid syntax. Option D tries to select from jsonb_array_elements but does not specify the column properly. Option D uses two separate inserts, less efficient.

🔧 Debug

advanced

Why does this JSON insertion fail with a syntax error?

Given the query:

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

Why does this cause a syntax error?

PostgreSQL

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

A The number 100 must be in quotes to be valid JSON.

B Double quotes inside single quotes cause syntax errors in SQL.

C JSON keys must be in double quotes, so customer should be "customer".

D The VALUES keyword is missing.

💡 Hint

Check JSON syntax rules for keys and strings.

JSON requires keys to be double-quoted strings. The given JSON uses unquoted keys, causing a syntax error.

🧠 Conceptual

expert

What happens if you insert invalid JSON into a jsonb column?

You try to run:

INSERT INTO events (data) VALUES ('{event: start, time: now()}');

What will PostgreSQL do?

PostgreSQL

INSERT INTO events (data) VALUES ('{event: start, time: now()}');

A Insert NULL into the jsonb column.

B Raise a syntax error because the JSON is invalid.

C Automatically convert the invalid JSON to valid JSON.

D Insert the data as text without error.

💡 Hint

PostgreSQL enforces JSON validity on jsonb columns.

PostgreSQL checks JSON validity on insert. Invalid JSON causes an error and the insert fails.

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