Arrow operators (-> and ->>) in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this JSON arrow operator query?

Given the table users with a JSONB column data containing {"name": "Alice", "age": 30}, what does this query return?

SELECT data->'name' FROM users WHERE id = 1;

PostgreSQL

CREATE TABLE users (id INT, data JSONB);
INSERT INTO users VALUES (1, '{"name": "Alice", "age": 30}');

A 'Alice'

B Alice

C "Alice"

D 30

💡 Hint

The -> operator returns a JSON object, including quotes for strings.

The -> operator returns the JSON value as JSON, so strings are quoted. The value is "Alice" including quotes.

❓ query_result

intermediate

What does the ->> operator return in this query?

Using the same users table, what is the output of:

SELECT data->>'age' FROM users WHERE id = 1;

PostgreSQL

CREATE TABLE users (id INT, data JSONB);
INSERT INTO users VALUES (1, '{"name": "Alice", "age": 30}');

A 30

B '30'

C "30"

D NULL

💡 Hint

The ->> operator returns text, not JSON.

The ->> operator returns the JSON value as text, so the number 30 is returned as text without quotes.

📝 Syntax

advanced

Which query correctly extracts the city name from this JSONB column?

Given a locations table with a JSONB column info containing {"address": {"city": "Boston", "zip": "02101"}}, which query returns the city name as text?

PostgreSQL

CREATE TABLE locations (id INT, info JSONB);
INSERT INTO locations VALUES (1, '{"address": {"city": "Boston", "zip": "02101"}}');

A SELECT info->>'address'->>'city' FROM locations WHERE id = 1;

B SELECT info->>'address'->'city' FROM locations WHERE id = 1;

C SELECT info->'address'->'city' FROM locations WHERE id = 1;

D SELECT info->'address'->>'city' FROM locations WHERE id = 1;

💡 Hint

Use -> to get JSON object, then ->> to get text value.

Option D first extracts the 'address' JSON object with ->, then extracts 'city' as text with ->>, returning 'Boston' as text.

🧠 Conceptual

advanced

What is the difference between -> and ->> operators in PostgreSQL JSONB?

Choose the correct explanation of the difference between the two operators.

A -> returns JSON object or array; ->> returns text value without JSON formatting.

B -> returns text value; ->> returns JSON object or array.

C Both return JSON objects but differ in performance.

D -> returns integer values only; ->> returns string values only.

💡 Hint

Think about the data type each operator returns.

The -> operator returns JSON data including quotes for strings, while ->> returns plain text without JSON formatting.

❓ query_result

expert

What is the output of this nested JSON arrow operator query?

Given a table events with JSONB column details containing:

{"event": {"name": "Concert", "location": {"city": "NYC", "venue": "Madison Square Garden"}}}

What does this query return?

SELECT details->'event'->'location'->>'venue' FROM events WHERE id = 1;

PostgreSQL

CREATE TABLE events (id INT, details JSONB);
INSERT INTO events VALUES (1, '{"event": {"name": "Concert", "location": {"city": "NYC", "venue": "Madison Square Garden"}}}');

A NULL

B Madison Square Garden

C 'Madison Square Garden'

D "Madison Square Garden"

💡 Hint

The last operator ->> returns text without quotes.

The query extracts the nested 'venue' value as text, so the output is the string without quotes.

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