JSON vs JSONB differences in PostgreSQL - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a table with a JSONB column.

PostgreSQL

CREATE TABLE data_store (info [1]);

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

AJSONB

BTEXT

CINTEGER

DVARCHAR

Attempts:

3 left

💡 Hint

Common Mistakes

Using TEXT or VARCHAR instead of JSONB for JSON data storage.

Confusing JSON with JSONB types.

The JSONB type stores JSON data in a binary format, allowing efficient querying and indexing.

2fill in blank

medium

Complete the query to select rows where the JSONB column contains a key 'name'.

PostgreSQL

SELECT * FROM data_store WHERE info [1] 'name';

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

A?

B@>

C->

D->>

Attempts:

3 left

💡 Hint

Common Mistakes

Using the containment operator @> instead of key existence operator.

Using JSON operators that extract values instead of checking keys.

The ? operator checks if the JSONB column contains the specified key.

3fill in blank

hard

Fix the error in the query to extract the text value of key 'age' from a JSONB column.

PostgreSQL

SELECT info->>[1] FROM data_store;

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

A'"age"'

Bage

C"age"

D'age'

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting quotes around the key name.

Using double quotes instead of single quotes.

The key name must be a string literal in single quotes inside the operator ->>.

4fill in blank

hard

Fill both blanks to create a JSONB object with keys 'name' and 'age'.

PostgreSQL

SELECT jsonb_build_object([1], 'Alice', [2], 30);

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

A'name'

B'age'

Cname

Dage

Attempts:

3 left

💡 Hint

Common Mistakes

Using unquoted keys which causes syntax errors.

Mixing quoted and unquoted keys.

Keys in JSONB objects must be string literals, so use single quotes around 'name' and 'age'.

5fill in blank

hard

Fill all three blanks to update the JSONB column by adding a new key 'city' with value 'Paris'.

PostgreSQL

UPDATE data_store SET info = info [1] jsonb_build_object([2], [3]);

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

A||

B'city'

C'Paris'

D->

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong operator like -> instead of ||.

Not quoting the key or value strings.

The || operator merges JSONB objects. Use string literals for key and value.