Why JSON support matters in PostgreSQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select the JSON data from the column named 'data'.

PostgreSQL

SELECT [1] FROM my_table;

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

A'data'

Bjson

Cjson_data

Ddata

Attempts:

3 left

💡 Hint

Common Mistakes

Using quotes around the column name, which makes it a string literal.

Using a wrong column name.

To select a column in SQL, just use its name without quotes.

2fill in blank

medium

Complete the code to extract the value of the key 'name' from the JSON column 'data'.

PostgreSQL

SELECT data[1]'name' FROM my_table;

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

A#>>

B->>

C->

D#>

Attempts:

3 left

💡 Hint

Common Mistakes

Using -> which returns JSON instead of text.

Using the wrong operator for key extraction.

The operator ->> extracts the JSON object field as text.

3fill in blank

hard

Fix the error in the query to filter rows where the JSON key 'age' is greater than 30.

PostgreSQL

SELECT * FROM my_table WHERE (data->>[1])::int > 30;

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

A'age'

Bage

C"age"

Ddata

Attempts:

3 left

💡 Hint

Common Mistakes

Not quoting the key name, causing syntax errors.

Using double quotes instead of single quotes.

JSON keys must be in single quotes inside the operator.

4fill in blank

hard

Fill both blanks to create a JSON object with keys 'name' and 'age' from columns.

PostgreSQL

SELECT json_build_object([1], name, [2], age) FROM my_table;

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

A'name'

B'age'

Cname

Dage

Attempts:

3 left

💡 Hint

Common Mistakes

Using column names as keys without quotes.

Mixing up keys and values.

Keys in JSON objects must be strings in quotes.

5fill in blank

hard

Fill all three blanks to filter rows where the JSON key 'status' equals 'active'.

PostgreSQL

SELECT * FROM my_table WHERE data[1]'status' [2] [3];

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

A->>

B=

C'active'

D->

Attempts:

3 left

💡 Hint

Common Mistakes

Using -> which returns JSON, not text.

Not quoting the string 'active'.

Using wrong comparison operators.

Use ->> to extract text, then compare with '=' to the string 'active'.

Practice

1. Why is JSON support important in PostgreSQL?

easy

A. It makes PostgreSQL faster than all other databases.

B. It allows storing flexible and varying data structures in one column.

C. It replaces the need for any relational tables.

D. It automatically converts JSON to XML format.

Solution

1. Step 1: Understand JSON flexibility

   JSON allows storing data that can change structure without altering the table schema.

2. Step 2: Compare with relational tables

   Relational tables require fixed columns, but JSON columns can hold different keys and values per row.

3. Final Answer:

   It allows storing flexible and varying data structures in one column. -> Option B

4. Quick Check:

   JSON = flexible data storage [OK]

Hint: JSON stores flexible data in one column easily [OK]

Common Mistakes:

• Thinking JSON makes PostgreSQL faster than all others
• Believing JSON replaces relational tables completely
• Confusing JSON with XML conversion

2. Which of the following is the correct way to declare a JSON column in a PostgreSQL table?

easy

A. CREATE TABLE data (info XML);

B. CREATE TABLE data (info TEXT[]);

C. CREATE TABLE data (info VARCHAR);

D. CREATE TABLE data (info JSON);

Solution

1. Step 1: Identify JSON data type syntax

   PostgreSQL uses the JSON or JSONB data type to store JSON data.

2. Step 2: Check each option

   Only CREATE TABLE data (info JSON); uses JSON type correctly; others use unrelated types.

3. Final Answer:

   CREATE TABLE data (info JSON); -> Option D

4. Quick Check:

   JSON column = JSON type [OK]

Hint: Use JSON or JSONB type for JSON columns [OK]

Common Mistakes:

• Using TEXT[] or VARCHAR for JSON data
• Confusing JSON with XML data type
• Missing JSON keyword in column definition

3. Given the table users with a JSON column profile, what does this query return?

SELECT profile->>'name' FROM users WHERE profile->>'age' = '30';

medium

A. An error because ->> cannot be used in WHERE.

B. All JSON profiles where age is 30.

C. Names of users whose age is 30 as text values.

D. Age values of users named '30'.

Solution

1. Step 1: Understand JSON operators

   The operator ->> extracts JSON object field as text. Here, profile->>'age' gets age as text.

2. Step 2: Analyze query logic

   The WHERE clause filters rows where age equals '30'. The SELECT returns the name field as text for those rows.

3. Final Answer:

   Names of users whose age is 30 as text values. -> Option C

4. Quick Check:

   ->> extracts text field = names for age 30 [OK]

Hint: ->> extracts text; WHERE filters JSON text fields [OK]

Common Mistakes:

• Thinking ->> cannot be used in WHERE clause
• Expecting full JSON objects instead of text
• Confusing age and name fields

4. What is wrong with this query that tries to filter JSON data?

SELECT * FROM orders WHERE details->'price' = 100;

medium

A. Using -> returns JSON, so comparison with integer 100 fails.

B. The table name 'orders' is invalid in PostgreSQL.

C. The operator -> cannot be used in WHERE clause.

D. JSON data cannot be filtered in PostgreSQL.

Solution

1. Step 1: Understand JSON operators and types

   The operator -> returns JSON type, not a plain value, so comparing it directly to integer 100 causes a type mismatch.

2. Step 2: Correct usage for comparison

   Use ->> to extract text and then cast to integer for numeric comparison.

3. Final Answer:

   Using -> returns JSON, so comparison with integer 100 fails. -> Option A

4. Quick Check:

   -> returns JSON, not scalar, so comparison fails [OK]

Hint: Use ->> to get text before comparing JSON values [OK]

Common Mistakes:

• Comparing JSON object directly to integer
• Assuming -> works like ->>
• Believing JSON cannot be filtered in WHERE

5. You have a table events with a JSONB column data storing event details. You want to find all events where the nested JSON key location.city is 'Paris'. Which query correctly achieves this?

hard

A. SELECT * FROM events WHERE data->'location'->>'city' = 'Paris';

B. SELECT * FROM events WHERE data->>'location.city' = 'Paris';

C. SELECT * FROM events WHERE data->'location.city' = 'Paris';

D. SELECT * FROM events WHERE data->'location'->'city' = 'Paris';

Solution

1. Step 1: Access nested JSON keys correctly

   To get nested keys, chain -> for JSON objects and ->> for text extraction. Here, data->'location' gets the location object, then ->>'city' extracts city as text.

2. Step 2: Check each option

   data->>'location.city' treats 'location.city' as a single key. data->'location.city' does the same with ->. data->'location'->'city' returns JSON for 'city', causing type mismatch when compared to 'Paris'. Only data->'location'->>'city' extracts text correctly.

3. Final Answer:

   SELECT * FROM events WHERE data->'location'->>'city' = 'Paris'; -> Option A

4. Quick Check:

   Nested keys need chained -> and ->> [OK]

Hint: Chain -> for objects, ->> for text extraction in JSON [OK]

Common Mistakes:

• Using dot notation inside ->> operator
• Comparing JSON object directly to string
• Not chaining operators for nested keys