Inserting JSON data in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to insert a JSON object into the table.

PostgreSQL

INSERT INTO products (data) VALUES ('[1]');

Drag options to blanks, or click blank then click option'

A'name': 'Book', 'price': 12.99

Bname: Book, price: 12.99

C["name", "Book"]

D{"name": "Book", "price": 12.99}

Attempts:

3 left

💡 Hint

Common Mistakes

Using single quotes for keys or string values inside JSON.

Using array brackets instead of curly braces for JSON objects.

The JSON object must be a valid JSON string. Option D is correctly formatted JSON.

2fill in blank

medium

Complete the code to insert JSON data using the jsonb type.

PostgreSQL

INSERT INTO orders (order_info) VALUES ([1]);

Drag options to blanks, or click blank then click option'

A'{id: 101, status: "shipped"}'::jsonb

B'{"id": 101, "status": shipped}'::jsonb

C'{"id": 101, "status": "shipped"}'::jsonb

D'{"id": 101, "status": "shipped"}'

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting to cast the string to jsonb.

Missing double quotes around string values.

To insert JSONB data, the string must be cast to jsonb type. Option C correctly casts the JSON string.

3fill in blank

hard

Fix the error in the INSERT statement for JSON data.

PostgreSQL

INSERT INTO users (profile) VALUES ('[1]'::json);

Drag options to blanks, or click blank then click option'

A'{"age": 30, "city": "NY"}'

C{"age": 30, city: "NY"}

D'{age: 30, city: NY}'

Attempts:

3 left

💡 Hint

Common Mistakes

Using single quotes inside JSON instead of double quotes.

Not enclosing the JSON string in single quotes in SQL.

The JSON string must be enclosed in single quotes outside and use double quotes inside. Option A is correct because it is a valid JSON string literal.

4fill in blank

hard

Fill both blanks to insert JSON data with a nested object.

PostgreSQL

INSERT INTO events (details) VALUES ('[1]'::[2]);

Drag options to blanks, or click blank then click option'

A{"event": "login", "user": {"id": 5}}

Bjson

Cjsonb

D{"event": login, "user": {id: 5}}

Attempts:

3 left

💡 Hint

Common Mistakes

Using unquoted keys or string values inside JSON.

Casting to json instead of jsonb when jsonb is preferred.

The JSON string must be valid with double quotes. Casting to jsonb is preferred for better performance.

5fill in blank

hard

Fill all three blanks to insert JSON data with an array and cast it properly.

PostgreSQL

INSERT INTO catalog (info) VALUES ('[1]'::[2]); -- array has [3] items

Drag options to blanks, or click blank then click option'

A{"products": ["pen", "notebook", "eraser"]}

Bjsonb

C3

D{"products": [pen, notebook, eraser]}

Attempts:

3 left

💡 Hint

Common Mistakes

Not quoting string values inside the JSON array.

Casting to json instead of jsonb.

Incorrectly counting the number of items in the array.

The JSON string must have double quotes around strings in the array. Casting to jsonb is correct. The array has 3 items.

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