Week 9

Types of Databases

Databases: Comparison, Choosing & Polyglot

jsonb: Concepts & Setup

jsonb: querying updating

jsonb: Indexes, Tradeoffs & Exercise

Object Storage

Practice

Assignment

• Teachers

Backend Track

What you will learn on this page

• How to extract JSON fields as text using >>
• How to navigate nested JSON objects using >
• How to query arrays inside JSON
• How to match multiple JSON fields at once using @>
• How to update a single JSON field using jsonb_set

What we are building

You are continuing the books API from the previous page. You already have three books in the database - a novel, a textbook, and a programming guide with nested specs and tags.

On this page you will add endpoints to search and update books using JSON operators. Each section adds one method to BookRepository.java and one endpoint to BookController.java.

By the end of this page your API will support:

5. Querying Nested Fields — > and >>

Concept

Two operators extract values from JSON:

Chain -> to go deeper, then ->> at the end to get plain text:

Add to BookRepository.java

// ->> extracts as plain text — safe to compare with =
public List<Book> findByAuthor(String author) {
    return jdbcTemplate.query(
            "SELECT * FROM books WHERE details->>'author' = ?",
            this::mapRow, author);
}

public List<Book> findByGenre(String genre) {
    return jdbcTemplate.query(
            "SELECT * FROM books WHERE details->>'genre' = ?",
            this::mapRow, genre);
}

// -> navigates into specs object, ->> extracts ram as text
public String findRam(Long id) {
    return jdbcTemplate.queryForObject(
            "SELECT details->'specs'->>'ram' FROM books WHERE id = ?",
            String.class, id);
}

Add to BookController.java

@GetMapping(params = "author")
public List<Book> getByAuthor(@RequestParam String author) {
    return bookRepository.findByAuthor(author);
}

@GetMapping(params = "genre")
public List<Book> getByGenre(@RequestParam String genre) {
    return bookRepository.findByGenre(genre);
}

@GetMapping("/{id}/ram")
public ResponseEntity<String> getRam(@PathVariable Long id) {
    return ResponseEntity.ok(bookRepository.findRam(id));
}

Test

GET /api/books?author=Tolkien    → The Hobbit 
GET /api/books?genre=Fantasy     → The Hobbit 
GET /api/books/3/ram             → 16GB 

6. Querying Arrays Within JSON

Concept

Use -> with a number to get an element from a JSON array (zero-based index):

details->'tags'->0    →  "java"         (first element)
details->'tags'->1    →  "programming"  (second element)

Add to BookRepository.java

// ->0 gets the first element of the tags array
public String findFirstTag(Long id) {
    return jdbcTemplate.queryForObject(
            "SELECT details->'tags'->>0 FROM books WHERE id = ?",
            String.class, id);
}

Add to BookController.java

@GetMapping("/{id}/first-tag")
public ResponseEntity<String> getFirstTag(@PathVariable Long id) {
    return ResponseEntity.ok(bookRepository.findFirstTag(id));
}

Test

GET /api/books/3/first-tag    → java 

Try changing ->>0 to ->>1 — you will get programming instead.

7. Containment Operator - @>

Concept

@> checks if the left JSON contains the right JSON. The real power is matching multiple fields at once - something ->> cannot do.

Add to BookRepository.java

// Single field search using @>
public List<Book> findByGenreContainment(String genre) {
    return jdbcTemplate.query(
            "SELECT * FROM books WHERE details @> CAST(? AS jsonb)",
            this::mapRow,
            "{\"genre\": \"" + genre + "\"}");
}

// Multi-field search — the real power of @>
public List<Book> findByAuthorAndGenre(String author, String genre) {
    return jdbcTemplate.query(
            "SELECT * FROM books WHERE details @> CAST(? AS jsonb)",
            this::mapRow,
            "{\"author\": \"" + author + "\", \"genre\": \"" + genre + "\"}");
}

Add to BookController.java

@GetMapping(value = "/search", params = "genre")
public List<Book> searchByGenre(@RequestParam String genre) {
    return bookRepository.findByGenreContainment(genre);
}

@GetMapping(value = "/search", params = {"author", "genre"})
public List<Book> searchByAuthorAndGenre(
        @RequestParam String author,
        @RequestParam String genre) {
    return bookRepository.findByAuthorAndGenre(author, genre);
}

Test

GET /api/books/search?genre=Fantasy
→ all Fantasy books 

GET /api/books/search?author=Tolkien&genre=Fantasy
→ only books where BOTH match 

GET /api/books/search?author=Tolkien&genre=Programming
→ [] — Tolkien has no Programming books 

8. Updating JSON Data — jsonb_set

Concept

jsonb_set updates one field without replacing the whole JSON object:

Strings vs numbers: string values need inner quotes "\"Sci-Fi\"". Number values do not — just pass "350" directly.

Add to BookRepository.java

java

// String field — needs inner quotes
public void updateGenre(Long id, String genre) {
    jdbcTemplate.update(
            "UPDATE books SET details = jsonb_set(details, '{genre}', ?::jsonb) WHERE id = ?",
            "\"" + genre + "\"", id);
}

// Number field — no inner quotes needed
public void updatePages(Long id, String pages) {
    jdbcTemplate.update(
            "UPDATE books SET details = jsonb_set(details, '{pages}', ?::jsonb) WHERE id = ?",
            pages, id);
}

// Nested field — path uses comma: {specs,ram}
public void updateRam(Long id, String ram) {
    jdbcTemplate.update(
            "UPDATE books SET details = jsonb_set(details, '{specs,ram}', ?::jsonb) WHERE id = ?",
            "\"" + ram + "\"", id);
}

Add to BookController.java

@PatchMapping("/{id}/genre")
public ResponseEntity<Void> updateGenre(
        @PathVariable Long id, @RequestParam String value) {
    bookRepository.updateGenre(id, value);
    return ResponseEntity.noContent().build();
}

@PatchMapping("/{id}/pages")
public ResponseEntity<Void> updatePages(
        @PathVariable Long id, @RequestParam String value) {
    bookRepository.updatePages(id, value);
    return ResponseEntity.noContent().build();
}

@PatchMapping("/{id}/ram")
public ResponseEntity<Void> updateRam(
        @PathVariable Long id, @RequestParam String value) {
    bookRepository.updateRam(id, value);
    return ResponseEntity.noContent().build();
}

Test

PATCH /api/books/1/genre?value=Sci-Fi    → 204 No Content 
GET  /api/books                          → only genre changed, author + pages untouched ✅
PATCH /api/books/1/pages?value=350       → 204 No Content 
PATCH /api/books/3/ram?value=32GB        → 204 No Content 

Verify in the database:

docker exec -it my-postgres psql -U hyfuser -d jsonb_demo

SELECT title, details->>'genre' AS genre, details->>'pages' AS pages FROM books;

Operator Quick Reference