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.

Practice

1. What is the main purpose of creating a JSON column in a PostgreSQL table?

easy

A. To enforce strict relational data types

B. To create indexes automatically

C. To speed up numeric calculations

D. To store flexible, structured data inside a single column

Solution

1. Step 1: Understand JSON column usage

   JSON columns allow storing data that can have varying structure, unlike fixed columns.

2. Step 2: Compare with other options

   Options A, C, and D do not describe JSON columns' purpose; they relate to other database features.

3. Final Answer:

   To store flexible, structured data inside a single column -> Option D

4. Quick Check:

   JSON columns = flexible data storage [OK]

Hint: JSON columns store flexible data, not fixed types [OK]

Common Mistakes:

• Thinking JSON columns enforce strict types
• Confusing JSON with indexing features
• Assuming JSON speeds numeric calculations

2. Which of the following is the correct syntax to create a table with a JSON column named data?

easy

A. CREATE TABLE mytable (id INT, data JSONB STRING);

B. CREATE TABLE mytable (id INT, data JSON);

C. CREATE TABLE mytable (id INT, data TEXT JSON);

D. CREATE TABLE mytable (id INT, data STRING);

Solution

1. Step 1: Recall JSON column syntax

   In PostgreSQL, JSON columns are declared with type JSON or JSONB directly.

2. Step 2: Check each option

   CREATE TABLE mytable (id INT, data JSON); uses correct syntax. Options A, B, and C use invalid or mixed types.

3. Final Answer:

   CREATE TABLE mytable (id INT, data JSON); -> Option B

4. Quick Check:

   JSON column syntax = CREATE TABLE mytable (id INT, data JSON); [OK]

Hint: Use JSON or JSONB as column type directly [OK]

Common Mistakes:

• Using STRING or TEXT with JSON incorrectly
• Combining JSON with other types in declaration
• Missing commas or parentheses

3. Given the table created as CREATE TABLE products (id SERIAL, info JSONB); and the insert statement:
INSERT INTO products (info) VALUES ('{"name": "Pen", "price": 1.5}');
What will be the result of the query:
SELECT info->>'name' FROM products WHERE id = 1;

medium

A. {"name": "Pen", "price": 1.5}

B. 1.5

C. Pen

D. NULL

Solution

1. Step 1: Understand JSONB insert and query

   The JSONB column stores the JSON object. The operator ->> extracts the text value of the key.

2. Step 2: Analyze the query

   The query selects the 'name' field as text from the JSONB column for id 1, which is 'Pen'.

3. Final Answer:

   Pen -> Option C

4. Quick Check:

   info->>'name' = 'Pen' [OK]

Hint: Use ->> to get JSON text value by key [OK]

Common Mistakes:

• Using -> instead of ->> returns JSON, not text
• Expecting full JSON instead of single value
• Not matching the correct id in WHERE clause

4. You try to create a table with a JSON column using:
CREATE TABLE users (id INT, profile JSONB DEFAULT {});
But get a syntax error. What is the problem?

medium

A. Missing quotes around the default JSON value

B. JSONB columns cannot have default values

C. Empty braces {} are not valid default for JSONB

D. INT type cannot be used with JSONB columns

Solution

1. Step 1: Check default value syntax for JSONB

   Default JSON values must be valid JSON strings, so '{}' must be in single quotes.

2. Step 2: Identify the error cause

   Using {} without quotes causes syntax error. Correct is DEFAULT '{}'.

3. Final Answer:

   Missing quotes around the default JSON value -> Option A

4. Quick Check:

   Default JSON must be quoted [OK]

Hint: Always quote JSON default values as strings [OK]

Common Mistakes:

• Using unquoted braces {} as default
• Assuming JSONB cannot have defaults
• Confusing data types in table definition

5. You want to create a table orders with a JSONB column details that stores order info. You also want to ensure each order has a customer_id and a details JSON with a total field as a number. Which approach correctly creates the table and enforces the total field presence?

hard

A. CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT NOT NULL, details JSONB NOT NULL CHECK (details->>'total' IS NOT NULL));

B. CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT, details JSONB DEFAULT '{}');

C. CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT NOT NULL, details JSONB NOT NULL);

D. CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT NOT NULL, details JSONB NOT NULL CHECK (details ? 'total'));

Solution

1. Step 1: Understand JSONB constraints

   To ensure 'total' key exists and is not null, use a CHECK with details->>'total' IS NOT NULL.

2. Step 2: Evaluate options

   CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT NOT NULL, details JSONB NOT NULL CHECK (details ? 'total')); checks only key presence (details ? 'total'), but does not check value is not null. CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT NOT NULL, details JSONB NOT NULL CHECK (details->>'total' IS NOT NULL)); checks both key and value presence correctly.

3. Final Answer:

   CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT NOT NULL, details JSONB NOT NULL CHECK (details->>'total' IS NOT NULL)); -> Option A

4. Quick Check:

   CHECK ensures 'total' key with value [OK]

Hint: Use CHECK with details->>'key' IS NOT NULL to enforce JSON key presence [OK]

Common Mistakes:

• Using ? operator without checking value
• Not adding NOT NULL constraints
• Setting default empty JSON without validation