Arrow operators (-> and ->>) in PostgreSQL

Introduction

Arrow operators help you get data from JSON columns easily. They let you pick parts of the JSON without extra work.

You have a JSON column in your table and want to get a specific key's value.

You want to extract a JSON object or array from a JSON column.

You need to get a text value from a JSON column for filtering or display.

You want to work with nested JSON data inside your database.

Syntax

PostgreSQL

json_column -> 'key'
json_column ->> 'key'

-> returns a JSON object or array.

->> returns plain text (string) from the JSON value.

Examples

Gets the JSON value for key 'name' from the 'data' JSON column.

PostgreSQL

SELECT data -> 'name' FROM users;

Gets the text value for key 'name' from the 'data' JSON column.

PostgreSQL

SELECT data ->> 'name' FROM users;

Gets the text value of 'city' inside the nested 'address' JSON object.

PostgreSQL

SELECT data -> 'address' ->> 'city' FROM users;

Sample Program

This example creates a table with a JSON column, inserts two users with nested JSON data, then shows how to get the 'address' JSON object and the 'city' text inside it.

PostgreSQL

CREATE TABLE users (id SERIAL PRIMARY KEY, data JSON);

INSERT INTO users (data) VALUES
  ('{"name": "Alice", "age": 30, "address": {"city": "Paris", "zip": "75000"}}'),
  ('{"name": "Bob", "age": 25, "address": {"city": "Berlin", "zip": "10115"}}');

-- Get the JSON object for 'address'
SELECT data -> 'address' AS address_json FROM users;

-- Get the city as text from the nested address
SELECT data -> 'address' ->> 'city' AS city FROM users;

Important Notes

Use -> when you want to keep the JSON format for further JSON operations.

Use ->> when you want plain text to use in comparisons or display.

If the key does not exist, both operators return NULL.

Summary

Arrow operators help extract data from JSON columns easily.

-> returns JSON; ->> returns text.

They work well for nested JSON data too.

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