Arrow operators (-> and ->>) in PostgreSQL - Mini Project: Build & Apply

Exploring Arrow Operators (-> and ->>) in PostgreSQL JSON Data

📖 Scenario: You work at a small online bookstore. The store keeps book details in a PostgreSQL table using JSON data type to store extra information about each book.Your task is to practice extracting data from JSON columns using PostgreSQL's arrow operators -> and ->>.

🎯 Goal: Build SQL queries that extract JSON objects and JSON text values from a books table using the -> and ->> operators.

📋 What You'll Learn

Create a books table with an id column and a details column of type JSON

Insert three rows with specific JSON data for each book

Write a query using the -> operator to extract the JSON object for the author key

Write a query using the ->> operator to extract the text value of the title key

💡 Why This Matters

🌍 Real World

Many modern applications store flexible data in JSON columns inside relational databases like PostgreSQL. Knowing how to extract and query JSON data is essential for working with such databases.

💼 Career

Database developers and backend engineers often need to query JSON data efficiently. Understanding arrow operators helps in writing clear and performant SQL queries.

1

Create the books table with JSON data

Create a table called books with two columns: id as an integer primary key, and details as a JSON column.

PostgreSQL

-- Create the books table with id and details columns
-- Your code here

Hint

Use SERIAL for auto-incrementing id and JSON as the data type for details.

2

Insert three rows with JSON book details

Insert three rows into books with these exact JSON details in the details column:
1. {"title": "The Hobbit", "author": {"first_name": "J.R.R.", "last_name": "Tolkien"}, "year": 1937}
2. {"title": "1984", "author": {"first_name": "George", "last_name": "Orwell"}, "year": 1949}
3. {"title": "To Kill a Mockingbird", "author": {"first_name": "Harper", "last_name": "Lee"}, "year": 1960}

PostgreSQL

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    details JSON
);
-- Insert the three rows with exact JSON details below
-- Your code here

Hint

Use INSERT INTO books (details) VALUES (...), (...), (...); with JSON strings exactly as shown.

3

Query to extract the author JSON object using ->

Write a SQL query that selects id and extracts the author JSON object from the details column using the -> operator. Name the extracted column author_info.

PostgreSQL

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    details JSON
);

INSERT INTO books (details) VALUES
('{"title": "The Hobbit", "author": {"first_name": "J.R.R.", "last_name": "Tolkien"}, "year": 1937}'),
('{"title": "1984", "author": {"first_name": "George", "last_name": "Orwell"}, "year": 1949}'),
('{"title": "To Kill a Mockingbird", "author": {"first_name": "Harper", "last_name": "Lee"}, "year": 1960}');

-- Select id and author JSON object using -> operator
-- Your code here

Hint

Use details->'author' to get the JSON object for the author.

4

Query to extract the title text using ->>

Write a SQL query that selects id and extracts the title text value from the details column using the ->> operator. Name the extracted column book_title.

PostgreSQL

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    details JSON
);

INSERT INTO books (details) VALUES
('{"title": "The Hobbit", "author": {"first_name": "J.R.R.", "last_name": "Tolkien"}, "year": 1937}'),
('{"title": "1984", "author": {"first_name": "George", "last_name": "Orwell"}, "year": 1949}'),
('{"title": "To Kill a Mockingbird", "author": {"first_name": "Harper", "last_name": "Lee"}, "year": 1960}');

SELECT id, details->'author' AS author_info FROM books;

-- Select id and title text using ->> operator
-- Your code here

Hint

Use details->>'title' to get the text value of the title.

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