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.