Arrow operators (-> and ->>) in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the PostgreSQL arrow operator -> do when used with JSON data?

The -> operator extracts a JSON object field by key and returns it as JSON, preserving the JSON format.

beginner

How is the ->> operator different from -> in PostgreSQL JSON queries?

The ->> operator extracts a JSON object field by key and returns the value as text, not as JSON.

beginner

Given a JSON column data with value {"name": "Alice", "age": 30}, what does data->'name' return?

It returns the JSON string "Alice" including the quotes, because it returns JSON format.

beginner

Given the same JSON column data, what does data->>'name' return?

It returns the text Alice without quotes, because it returns the value as plain text.

intermediate

Can the arrow operators -> and ->> be used to access elements inside JSON arrays in PostgreSQL?

Yes, you can use -> with an integer index to get a JSON element from an array, and ->> to get it as text.

What type of value does the PostgreSQL operator -> return when extracting a JSON field?

A JSON value

B Text value

C Integer value

D Boolean value

The -> operator returns the extracted field as JSON, preserving its JSON format.

Which operator would you use to get a JSON field value as plain text in PostgreSQL?

A ->>

B @>

C #>

D ->

The ->> operator extracts the JSON field and returns it as text.

If data is a JSON column with {"age": 25}, what does data->>'age' return?

A Number 25

B "25"

C Text 25

D 25

The ->> operator returns the value as text without quotes, so it returns the string '25'.

Can you use the arrow operators to access elements inside a JSON array by index?

A Only with -> but not ->>

B No, arrow operators only work with object keys

C Yes, using an integer index with -> or ->>

D Only with ->> but not ->

You can use -> or ->> with an integer index to access JSON array elements.

What will data->'missing_key' return if the key does not exist in the JSON object?

A Empty string

B NULL

C Error

D Zero

If the key is missing, the operator returns NULL.

Explain how the PostgreSQL arrow operators -> and ->> differ when extracting data from JSON columns.

Think about the data type returned and how you might use the result.

Describe how to access an element inside a JSON array stored in a PostgreSQL column using arrow operators.

Remember arrays use numbers as keys.

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