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.