Creating JSON columns in PostgreSQL - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a table with a JSON column named 'data'.

PostgreSQL

CREATE TABLE users (id SERIAL PRIMARY KEY, [1] JSON);

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

Ainfo

Bdata

Cjson_data

Ddetails

Attempts:

3 left

💡 Hint

Common Mistakes

Using a different column name than 'data'.

Forgetting to specify the JSON type.

The column name should be 'data' as specified. The type JSON allows storing JSON objects.

2fill in blank

medium

Complete the code to add a JSONB column named 'attributes' to the existing table 'products'.

PostgreSQL

ALTER TABLE products ADD COLUMN [1] JSONB;

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

Adetails

Bdata

Cinfo

Dattributes

Attempts:

3 left

💡 Hint

Common Mistakes

Using JSON instead of JSONB when JSONB is requested.

Using a wrong column name.

The column to add is named 'attributes' and should be of type JSONB for efficient JSON storage.

3fill in blank

hard

Fix the error in the code to create a table with a JSON column named 'profile'.

PostgreSQL

CREATE TABLE users (id SERIAL PRIMARY KEY, profile [1]);

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

AJSON

BVARCHAR

CTEXT

DINT

Attempts:

3 left

💡 Hint

Common Mistakes

Using TEXT or VARCHAR instead of JSON.

Using INT which is not suitable for JSON data.

The column 'profile' must be of type JSON to store JSON data. TEXT or VARCHAR would store plain text, not JSON.

4fill in blank

hard

Fill both blanks to create a table 'orders' with a JSONB column 'order_info' and a JSON column 'metadata'.

PostgreSQL

CREATE TABLE orders (id SERIAL PRIMARY KEY, order_info [1], metadata [2]);

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

AJSONB

BTEXT

CJSON

DVARCHAR

Attempts:

3 left

💡 Hint

Common Mistakes

Using TEXT or VARCHAR instead of JSON or JSONB.

Mixing up JSON and JSONB types.

'order_info' should be JSONB for efficient JSON storage and indexing, while 'metadata' is JSON type.

5fill in blank

hard

Fill all three blanks to create a table 'events' with columns: 'id' as primary key, 'event_data' as JSONB, and 'extra_info' as JSON.

PostgreSQL

CREATE TABLE events (id [1] PRIMARY KEY, event_data [2], extra_info [3]);

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

ASERIAL

BJSONB

CJSON

DINTEGER

Attempts:

3 left

💡 Hint

Common Mistakes

Using INTEGER without auto-increment for 'id'.

Using TEXT or VARCHAR instead of JSON or JSONB.

'id' should be SERIAL for auto-increment primary key, 'event_data' as JSONB, and 'extra_info' as JSON.

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