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.