Creating JSON columns in PostgreSQL - Practice Exercises

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this JSON column insertion?

Consider the table users with a JSON column profile. After running the following insert, what will be the value stored in profile for user_id 1?

PostgreSQL

CREATE TABLE users (user_id SERIAL PRIMARY KEY, profile JSON);
INSERT INTO users (profile) VALUES ('{"name": "Alice", "age": 30}');
SELECT profile FROM users WHERE user_id = 1;

A {"name": "Alice", "age": 30}

B '{"name": "Alice", "age": 30}'

C {name: Alice, age: 30}

D SyntaxError

💡 Hint

Remember JSON columns store valid JSON objects, not strings with quotes.

The JSON column stores the JSON object as is, without extra quotes. Option A shows the correct JSON object stored.

🧠 Conceptual

intermediate

Which data type is best for storing JSON in PostgreSQL?

You want to store JSON data in a PostgreSQL table. Which data type should you use to store JSON that allows indexing and efficient querying?

A JSON

B TEXT

C JSONB

D VARCHAR

💡 Hint

One JSON type is binary and supports indexing.

JSONB stores JSON in a binary format, allowing indexing and faster queries compared to JSON which stores text.

📝 Syntax

advanced

Which CREATE TABLE statement correctly defines a JSONB column?

Select the valid PostgreSQL statement to create a table events with a JSONB column named data.

A CREATE TABLE events (id SERIAL PRIMARY KEY, data VARCHAR);

B CREATE TABLE events (id SERIAL PRIMARY KEY, data JSONB);

C CREATE TABLE events (id SERIAL PRIMARY KEY, data JSONB NOT NULL DEFAULT '{}');

D CREATE TABLE events (id SERIAL PRIMARY KEY, data JSON);

💡 Hint

Check the exact syntax for JSONB column type.

Option B correctly defines a JSONB column. Option B uses JSON type, which is valid but not JSONB. Option B has invalid default syntax for JSONB. Option B uses VARCHAR which is not JSON.

❓ optimization

advanced

How to optimize queries on JSONB columns?

You have a JSONB column info in table products. Which approach improves query speed when filtering by a key inside the JSONB?

A Store JSON as TEXT and index it.

B Create a B-tree index on the JSONB column.

C No index needed; JSONB queries are always fast.

D Create a GIN index on the JSONB column.

💡 Hint

Think about index types that support JSONB operators.

GIN indexes support fast containment and key-existence queries on JSONB columns. B-tree indexes do not work well for JSONB. Storing JSON as TEXT prevents indexing inside JSON. Queries without indexes are slower.

🔧 Debug

expert

Why does this JSONB insert fail?

You run this insert: INSERT INTO logs (entry) VALUES ('{event: "login", user: 123}'); where entry is JSONB. Why does it fail?

A Keys and string values must be double-quoted in JSON.

B JSONB columns cannot store objects with numbers.

C Single quotes are not allowed around JSON strings.

D The table logs does not exist.

💡 Hint

Check JSON syntax rules for keys and strings.

JSON requires keys and string values to be double-quoted. The given JSON is invalid because keys are unquoted and string value uses double quotes inside single quotes incorrectly.