Arrow operators (-> and ->>) in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to extract the JSON object field using the arrow operator.

PostgreSQL

SELECT data[1]'name' FROM users;

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

A->

B#>>

C#>

D->>

Attempts:

3 left

💡 Hint

Common Mistakes

Using ->> instead of -> returns text, not JSON.

Using #> or #>> which are for JSON path extraction.

The -> operator extracts a JSON object field as JSON.

2fill in blank

medium

Complete the code to extract the text value of the JSON field using the arrow operator.

PostgreSQL

SELECT data[1]'age' FROM users;

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

A->>

B->

C#>

D#>>

Attempts:

3 left

💡 Hint

Common Mistakes

Using -> instead of ->> returns JSON, not text.

Using #> or #>> which require JSON path arrays.

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

3fill in blank

hard

Fix the error in the code to correctly extract the text value from a nested JSON object.

PostgreSQL

SELECT data->'address'[1]'city' FROM users;

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

A->

B->>

C#>

D#>>

Attempts:

3 left

💡 Hint

Common Mistakes

Using ->> for both extractions causes errors.

Using #> or #>> without proper JSON path array.

Use -> to get the nested JSON object address, then ->> to get the text value of city.

4fill in blank

hard

Complete the code to extract the text value of a nested JSON field using arrow operators.

PostgreSQL

SELECT data->'profile'[1]'email' FROM users;

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

A->

B->>

C#>

D#>>

Attempts:

3 left

💡 Hint

Common Mistakes

Using ->> for both blanks causes errors.

Using #> or #>> without JSON path arrays.

First extract the nested JSON object profile with ->, then extract the text value of email with ->>.

5fill in blank

hard

Complete the code to extract the text value of a deeply nested JSON field using arrow operators.

PostgreSQL

SELECT data->'settings'->'notifications'[1]'email' FROM users;

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

A->

B->>

C#>

D#>>

Attempts:

3 left

💡 Hint

Common Mistakes

Using ->> too early causes errors.

Using #> or #>> without JSON path arrays.

Use -> twice to navigate nested JSON objects settings and notifications, then ->> to extract the text value of email.

Practice

1. What does the PostgreSQL operator ->> do when used on a JSON column?

easy

A. Converts the entire JSON object to text

B. Extracts the value as JSON from a JSON object by key

C. Extracts the value as text from a JSON object by key

D. Deletes a key from the JSON object

Solution

1. Step 1: Understand the ->> operator

   The ->> operator extracts the value of a key from a JSON object and returns it as plain text.

2. Step 2: Compare with -> operator

   The -> operator returns the value as JSON, not text. So ->> is used when text output is needed.

3. Final Answer:

   Extracts the value as text from a JSON object by key -> Option C

4. Quick Check:

   ->> returns text [OK]

Hint: Remember: -> returns JSON, ->> returns text [OK]

Common Mistakes:

• Confusing -> and ->> operators
• Thinking ->> returns JSON instead of text
• Assuming ->> deletes keys

2. Which of the following is the correct syntax to extract the JSON value of key 'name' as JSON from a column data in table users?

easy

A. SELECT data->>name FROM users;

B. SELECT data->>'name' FROM users;

C. SELECT data->name FROM users;

D. SELECT data->'name' FROM users;

Solution

1. Step 1: Identify correct operator for JSON output

   To get the value as JSON, use -> with the key in single quotes.

2. Step 2: Check syntax correctness

   Keys must be in quotes. Options C and D miss quotes around 'name'. SELECT data->>'name' FROM users; uses ->> which returns text, not JSON.

3. Final Answer:

   SELECT data->'name' FROM users; -> Option D

4. Quick Check:

   Use -> with quoted key for JSON [OK]

Hint: Use single quotes around keys with -> or ->> [OK]

Common Mistakes:

• Omitting quotes around JSON keys
• Using ->> when JSON output is needed
• Using invalid syntax like ->name without quotes

3. Given the table products with a JSON column info containing {"price": 100, "details": {"color": "red"}}, what is the output of this query?

SELECT info->'details'->>'color' FROM products;

medium

A. red

B. NULL

C. { "color": "red" }

D. "red"

Solution

1. Step 1: Understand nested arrow operators

   The first -> extracts the JSON object at key 'details'. The second ->> extracts the text value of 'color'.

2. Step 2: Determine output format

   ->> returns text without quotes, so output is plain text 'red'.

3. Final Answer:

   red -> Option A

4. Quick Check:

   Nested -> and ->> extract text correctly [OK]

Hint: Use -> for JSON, ->> for text even in nested keys [OK]

Common Mistakes:

• Expecting JSON output with ->>
• Including quotes in text output
• Confusing nested operator order

4. What is wrong with this query?

SELECT data->>details->>'color' FROM items;

medium

A. Missing quotes around 'details' key

B. Using ->> before -> causes syntax error

C. Cannot chain arrow operators

D. No JSON column named 'data'

Solution

1. Step 1: Check key quoting in arrow operators

   Keys must be in single quotes. Here, 'details' is unquoted, causing error.

2. Step 2: Confirm chaining is allowed

   Chaining -> and ->> is valid if keys are quoted properly.

3. Final Answer:

   Missing quotes around 'details' key -> Option A

4. Quick Check:

   Always quote keys in arrow operators [OK]

Hint: Always put keys in single quotes when using -> or ->> [OK]

Common Mistakes:

• Forgetting quotes around JSON keys
• Assuming ->> can be used before -> without quotes
• Thinking chaining arrow operators is invalid

5. You have a table orders with a JSON column details storing nested JSON like {"customer": {"name": "Alice", "age": 30}}. Which query correctly extracts the customer's name as text?

hard

A. SELECT details->'customer'->'name' FROM orders;

B. SELECT details->'customer'->>'name' FROM orders;

C. SELECT details->>'customer'->>'name' FROM orders;

D. SELECT details->>'customer'->'name' FROM orders;

Solution

1. Step 1: Extract nested JSON object first

   Use -> to get the 'customer' JSON object from 'details'.

2. Step 2: Extract 'name' as text

   Use ->> on the 'name' key to get its value as text.

3. Step 3: Analyze other options

   SELECT details->'customer'->'name' FROM orders; returns JSON, not text. SELECT details->>'customer'->>'name' FROM orders; uses ->> on 'customer' which returns text, so next ->> fails. SELECT details->>'customer'->'name' FROM orders; mixes operators incorrectly.

4. Final Answer:

   SELECT details->'customer'->>'name' FROM orders; -> Option B

5. Quick Check:

   Use -> then ->> for nested text extraction [OK]

Hint: Use -> for JSON object, then ->> for text key inside it [OK]

Common Mistakes:

• Using ->> too early breaking JSON chain
• Mixing operator order incorrectly
• Expecting JSON output when text is needed