Lesson 3.1: Fetching simple data

The Fetch query

To begin with, we’ll use the following Fetch query to retrieve all data relating to paperback book entities in the database.

match $book isa paperback;
fetch { $book.* };

This is equivalent to the following SQL query.

SELECT *
FROM paperback;

This TypeQL query comprises two clauses: a match clause and a fetch clause.

The match clause is equivalent to a selection in SQL. It identifies what entity types (tables) we wish to retrieve data from. It can also specify further constraints that the retrieved data must meet, as we’ll see shortly. The fetch clause is equivalent to a projection in SQL. It specifies what attributes (columns) we would like to retrieve for the entities (rows) identified. In this case, we’ve chosen to retrieve all attributes using the attribute keyword.

Using read transaction, run this query. You should see the following result.

{ "title": "Classical Mythology", "page-count": 820 }
{ "title": "The Mummies of Urumchi", "page-count": 240 }
{ "title": "Business Secrets of The Pharoahs", "page-count": 260 }
{ "title": "To Kill a Mockingbird", "page-count": 281 }
{ "title": "Pride and Prejudice", "page-count": 295 }
{ "title": "Interpretation of Electron Diffraction Patterns", "page-count": 199 }
{ "title": "Hokusai's Fuji", "page-count": 416 }
{ "title": "Great Discoveries in Medicine", "page-count": 352 }
{ "title": "The Motorcycle Diaries: A Journey Around South America", "page-count": 160 }
{ "title": "The Hitchhiker's Guide to the Galaxy", "page-count": 215 }
{ "title": "One Hundred Years of Solitude", "page-count": 458 }
{ "title": "Pet Sematary", "page-count": 374 }

All Fetch queries return results in JSON format, where the output format matches the structure you write in the fetch clause syntax.

In the match clause of the query, we declared a single variable: $book. In TypeQL, variables are declared using a $ prefix. We also specified the type of $book to be paperback using the isa keyword. We can see that each JSON object returned represents an instance of paperback and contains a list of all that book’s attributes.

Exercise

Write a query to retrieve all the attributes of user entities. It should be equivalent to the following SQL query.

SELECT *
FROM users;

N.b. in the SQL query we need to use users rather than user as the latter is a reserved keyword in SQL. In general, singular nouns are preferred for entity type names.

Sample solution

match
$user isa user;
fetch { $user.* };

Projections

Looking at the list of each book’s attributes in the previous result, we can see that we’ve retrieved attributes of six types: isbn-13, isbn-10, title, genre, page-count, price, and stock. By modifying the fetch clause, we can choose to retrieve only specific attributes.

match
$book isa paperback;
fetch {
  "title": $book.title,
  "page-count": $book.page-count
};

If we run this query, we see the following result.

{
  "page-count": 820,
  "title": "Classical Mythology"
}
{
  "title": "The Mummies of Urumchi",
  "page-count": 240
}
{
  "title": "Business Secrets of The Pharoahs",
  "page-count": 260
}
{
  "page-count": 281,
  "title": "To Kill a Mockingbird"
}
{
  "title": "Pride and Prejudice",
  "page-count": 295
}
{
  "title": "Interpretation of Electron Diffraction Patterns",
  "page-count": 199
}
{
  "title": "Hokusai's Fuji",
  "page-count": 416
}
{
  "page-count": 352,
  "title": "Great Discoveries in Medicine"
}
{
  "page-count": 160,
  "title": "The Motorcycle Diaries: A Journey Around South America"
}
{
  "title": "The Hitchhiker's Guide to the Galaxy",
  "page-count": 215
}
{
  "title": "One Hundred Years of Solitude",
  "page-count": 458
}
{
  "title": "Pet Sematary",
  "page-count": 374
}

This time, only the titles and page counts of each book have been retrieved. Now this TypeQL query is equivalent to the following SQL query.

SELECT title, page_count
FROM paperback;

Exercise

Write a query to instead retrieve the isbn-13, price, and stock attributes of paperbacks.

Sample solution

match
$book isa paperback;
fetch {
  "isbn-13": $book.isbn-13,
  "price": $book.price,
  "stock": $book.stock
};

Selections

In the next query, we’ll add a constraint to the match clause, specifying that we want the details for a specific book with ISBN-13 "9780446310789".

match
$book isa paperback, has isbn-13 "9780446310789";
fetch {
  "title": $book.title,
  "page-count": $book.page-count
};

{
  "page-count": 281,
  "title": "To Kill a Mockingbird"
}

We can see from the result that we now only retrieve the data for the specific book we’re interested in. To do so we’ve used the has keyword, which is used to specify the value of an entity’s attribute, in this case the $book entity. In SQL, this query would be expressed in the following way.

SELECT title, page_count
FROM paperback
WHERE isbn_13 = '9780446310789';

Because TypeQL is composable, we could alternatively construct this query in the following equivalent way.

match
$book isa paperback;
$book has isbn-13 "9780446310789";
fetch {
  "title": $book.title,
  "page-count": $book.page-count
};

Try running these two queries. You should get the same results.

In the first version, we used a single composite statement in the match clause, whereas in the second version, we instead used two simple statements. If simple statements concern the same variable (in this case $book), we can always concatenate them using commas to form a composite statement, and vice versa.

Exercise

Write a query to retrieve the page-count and price attributes of the paperback with title "Great Discoveries in Medicine". Write the query once using a composite statement, and again using simple statements.

Sample solution

With a composite statement:

match
$book isa paperback, has title "Great Discoveries in Medicine";
fetch {
  "title": $book.title,
  "page-count": $book.page-count
};

With simple statements:

match
$book isa paperback;
$book has title "Great Discoveries in Medicine";
fetch {
  "title": $book.title,
  "page-count": $book.page-count
};

Entities and relations

There are two types of data objects in TypeDB: entities and relations. Entity types like book are used to represent application classes, while relation types are used to represent references between them. Relations must be instantiated with links to one or more role players, which play defined roles.

In order to represent a relation in TypeQL, we use tuple syntax of the following form.

$line isa order-line (order: $order, item: $book);

This statement signifies that:

$line is a relation of type order-line.
$order plays the role of order in $line.
$book plays the role of item in $line.

Here we are using order-line relations to represent the references that order entities make to book entities. In the following Fetch query, we retrieve the IDs of orders that include To Kill a Mockingbird and the quantity ordered.

match
$book isa paperback, has isbn-13 "9780446310789";
$line isa order-line (order: $order, item: $book);
fetch {
  "id": $order.id,
  "quantity": $line.quantity
};

{
  "id": "o0016",
  "quantity": 1
}
{
  "quantity": 1,
  "id": "o0032"
}
{
  "id": "o0036",
  "quantity": 2
}

This is equivalent to the following SQL query.

SELECT orders.id, order_line.quantity
FROM orders
INNER JOIN order_line ON order_line.order_id = orders.id
INNER JOIN paperback ON paperback.isbn_13 = order_line.item_id
WHERE paperback.isbn_13 = '9780446310789';

In a relational database, the relation type order-line would be represented by an associative table with foreign keys to the tables representing the entity types order and paperback¹. As a general rule, associative tables in relational databases can be mapped onto relation types in TypeDB.

A key difference here is that the TypeQL query uses roles to connect the order-line relation and its role players $order and $book, whereas the SQL query connects different rows based on literal value equalities. Simply sharing a variable between multiple statements is sufficient to describe the connections between data instances in TypeQL, without having to identify attribute values to join on (like the order ID and book ISBN).

Exercise

Modify the above query to also retrieve the status attribute of the order and the price attribute of the book.

Sample solution

#!test[schema]
#{{
#
# Licensed to the Apache Software Foundation (ASF) under one
# or more contributor license agreements.  See the NOTICE file
# distributed with this work for additional information
# regarding copyright ownership.  The ASF licenses this file
# to you under the Apache License, Version 2.0 (the
# "License"); you may not use this file except in compliance
# with the License.  You may obtain a copy of the License at
#
#   http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing,
# software distributed under the License is distributed on an
# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
# KIND, either express or implied.  See the License for the
# specific language governing permissions and limitations
# under the License.
#

define

entity book @abstract,
    owns isbn @card(0..2),
    owns isbn-13 @key,
    owns isbn-10 @unique,
    owns title,
    owns page-count,
    owns genre @card(0..),
    owns price,
    plays contribution:work,
    plays publishing:published,
    plays promotion-inclusion:item,
    plays order-line:item,
    plays rating:rated,
    plays recommendation:recommended;

entity hardback sub book,
    owns stock;

entity paperback sub book,
    owns stock;

entity ebook sub book;

entity contributor,
    owns name,
    plays contribution:contributor,
    plays authoring:author,
    plays editing:editor,
    plays illustrating:illustrator;

entity company @abstract,
    owns name;

entity publisher sub company,
    plays publishing:publisher;

entity courier sub company,
    plays delivery:deliverer;

entity publication,
    owns year,
    plays publishing:publication,
    plays locating:located;

entity user,
    owns id @key,
    owns name,
    owns birth-date,
    plays action-execution:executor,
    plays locating:located,
    plays recommendation:recipient;

entity order,
    owns id @key,
    owns status,
    plays order-line:order,
    plays action-execution:action,
    plays delivery:delivered;

entity promotion,
    owns code @key,
    owns name,
    owns start-timestamp,
    owns end-timestamp,
    plays promotion-inclusion:promotion;

entity review,
    owns id @key,
    owns score,
    owns verified,
    plays rating:review,
    plays action-execution:action;

entity login,
    owns success,
    plays action-execution:action;

entity address,
    owns street,
    plays delivery:destination,
    plays locating:located;

entity place @abstract,
    owns name,
    plays locating:located,
    plays locating:location;

entity city sub place;

entity state sub place;

entity country sub place;

relation contribution,
    relates contributor,
    relates work;

relation authoring sub contribution,
    relates author as contributor;

relation editing sub contribution,
    relates editor as contributor;

relation illustrating sub contribution,
    relates illustrator as contributor;

relation publishing,
    relates publisher,
    relates published,
    relates publication;

relation promotion-inclusion,
    relates promotion,
    relates item,
    owns discount;

relation order-line,
    relates order,
    relates item,
    owns quantity,
    owns price;

relation rating,
    relates review,
    relates rated;

relation action-execution,
    relates action,
    relates executor,
    owns timestamp;

relation delivery,
    relates deliverer,
    relates delivered,
    relates destination;

relation locating,
    relates located,
    relates location;

relation recommendation,
    relates recommended,
    relates recipient;

attribute isbn @abstract, value string;
attribute isbn-13 sub isbn;
attribute isbn-10 sub isbn;
attribute title, value string;
attribute page-count, value integer;
attribute genre, value string;
attribute stock, value integer;
attribute price, value double;
attribute discount, value double;
attribute id, value string;
attribute code, value string;
attribute name, value string;
attribute birth-date, value datetime;
attribute street, value string;
attribute year, value integer;
attribute quantity, value integer;
attribute score, value integer;
attribute verified, value boolean;
attribute timestamp, value datetime;
attribute start-timestamp, value datetime;
attribute end-timestamp, value datetime;
attribute status, value string @regex("^(paid|dispatched|delivered|returned|canceled)$");
attribute success, value boolean;

# TODO: Change to check
fun is_review_verified_by_purchase($review: review) -> { order }:
  match
    ($review, $product) isa rating;
    ($order, $product) isa order-line;
    ($user, $review) isa action-execution, has timestamp $review-time;
    ($user, $order) isa action-execution, has timestamp $order-time;
    $review-time > $order-time;
  return { $order };

fun book_recommendations_for($user: user) -> {book}:
  match
    $new-book isa book;
    {
        let $new-book in book_recommendations_by_author($user);
    } or {
        let $new-book in book_recommendations_by_genre($user);
    };
  return { $new-book };

fun book_recommendations_by_genre($user: user) -> { book }:
match
    $user isa user;
    $liked-book isa book;
    {
        ($user, $order-for-liked) isa action-execution;
        ($order-for-liked, $liked-book) isa order-line;
    } or {
        ($user, $review-for-liked) isa action-execution;
        ($review-for-liked, $liked-book) isa rating;
        $review-for-liked has score >= 7;
    };
    $new-book isa book;
    not { {
        ($user, $order-for-new) isa action-execution;
        ($order-for-new, $new-book) isa order-line;
    } or {
        ($user, $review-for-new) isa action-execution;
        ($review-for-new, $new-book) isa rating;
    }; };
    $liked-book has genre $shared-genre;
    $new-book has genre $shared-genre;
    not { {
        $shared-genre == "fiction";
    } or {
        $shared-genre == "nonfiction";
    }; };
  return { $new-book };

fun book_recommendations_by_author($user: user) -> { book }:
  match
    $user isa user;
    $liked-book isa book;
    {
        ($user, $order-for-liked) isa action-execution;
        ($order-for-liked, $liked-book) isa order-line;
    } or {
        ($user, $review-for-liked) isa action-execution;
        ($review-for-liked, $liked-book) isa rating;
        $review-for-liked has score >= 7;
    };
    $new-book isa book;
    not { {
        ($user, $order-for-new) isa action-execution;
        ($order-for-new, $new-book) isa order-line;
    } or {
        ($user, $review-for-new) isa action-execution;
        ($review-for-new, $new-book) isa rating;
    }; };
    ($liked-book, $shared-author) isa authoring;
    ($new-book, $shared-author) isa authoring;
  return { $new-book };

fun order_line_best_price($line: order-line) -> { double }:
  match
    ($order) isa action-execution, has timestamp $order-time;
    $line isa order-line, links ($order, $item);
    $item has price $retail-price;
    let $time_value = $order-time;
    let $best-discount = best_discount_for_item($item, $time_value);
    let $discounted-price = round(100 * $retail-price * (1 - $best-discount)) / 100;
    $line has quantity $quantity;
    let $line-total = $quantity * $discounted-price;
  return { $line-total };

fun best_discount_for_item($item: book, $order-time: datetime) -> double:
  match
    {
        $inclusion isa promotion-inclusion,
            links ($promotion, $item),
            has discount $discount-attr;
        $promotion has start-timestamp <= $order-time,
            has end-timestamp >= $order-time;
        let $discount = $discount-attr;
    } or {
        let $discount = 0.0; # default
    };
return max($discount);

fun transitive_places($place: place) -> { place }:
  match
    {
      locating (located: $place, location: $parent);
    } or {
      locating (located: $place, location: $middle);
      let $parent in transitive_places($middle);
    };
  return { $parent };
#}}
#!test[read]
match
$book isa paperback, has isbn-13 "9780446310789";
$line isa order-line (order: $order, item: $book);
fetch {
  "id": $order.id,
  "status": $order.status,
  "quantity": $line.quantity,
  "price": $book.price
};

Ternary relations

In the previous query, $line was a binary relation between the two role players $order and $book. However, the tuple syntax of relations is extremely flexible and allows us to use a tuple with a different number of elements to represent a relation with a different number of role players. In the next query, we extend the previous query by also retrieving the name of the courier that is delivering the order and the street address of the order’s destination.

This is an example of a ternary relation, and is written using a relation shorthand syntax, in which the relation variable and isa can be dropped when the variable is not needed. Compare this to the order line, which has the variable name $line, as we need to refer it in the fetch clause to retrieve the associated quantity. Under the hood, the system generates an anonymous variable which may be made visible during logging or debugging queries.

match
$book isa paperback, has isbn-13 "9780446310789";
$line isa order-line (order: $order, item: $book);
delivery (deliverer: $courier, delivered: $order, destination: $address);
fetch {
  "id": $order.id,
  "quantity": $line.quantity,
  "name": $courier.name,
  "street": $address.street
};

{
  "name": "FedEx",
  "street": "464 Pilgrim Lane",
  "quantity": 1,
  "id": "o0016"
}
{
  "name": "FedEx",
  "id": "o0032",
  "quantity": 1,
  "street": "984 Williams Street"
}
{
  "quantity": 2,
  "name": "DHL",
  "id": "o0036",
  "street": "20 Ridge Lane"
}

This ternary relation exists between three role players: $courier, $order, and $address. Higher order relations are used to represent rich references between multiple classes.

Exercise

In a relational database, a ternary relation would be represented by an associative table between three foreign key columns. Write a SQL query that is equivalent to the above TypeQL query.

Sample solution

SELECT orders.id, order_line.quantity, courier.name, address.street
FROM orders
INNER JOIN order_line ON order_line.order_id = orders.id
INNER JOIN paperback ON paperback.isbn_13 = order_line.item_id
INNER JOIN delivery ON delivery.delivered_id = orders.id
INNER JOIN courier ON courier.id = delivery.courier_id
INNER JOIN address ON address.id = delivery.address_id
WHERE paperback.isbn_13 = '9780446310789';

In the same way that we can use a tuple with two or three elements respectively to represent a binary or ternary relation, we can likewise use a tuple with n elements for an n-ary relation!

$rel isa n-ary-relation (role-1: $a, role-2: $b, role-3: $c, role-4: $d, ...);

This way, we can represent relations with any number of role players.

Footnotes

^ The seasoned SQL engineer will notice that, if paperbacks are not the only item that can be ordered, then we could not use a foreign key to reference them. We’d need a proper strategy for modeling the polymorphism in the model, likely one of Martin Fowler’s inheritance design patterns. If we go with the class-table inheritance pattern, then the foreign key would instead be to a product table. Conveniently, using this pattern means the isbn_13 column of the paperback table would reference the id column of the product table, so the SQL query shown would remain the same. As a polymorphic database, TypeDB is not affected by these architectural challenges! We’ll see how polymorphism is modeled in TypeDB in Lesson 5.

For LLMs & AI agents: this page as Markdown · llms.txt · llms-full.txt (TypeQL summary)

Services

Platform

Tools

What is TypeDB?

Why TypeDB?

Start building

By use case

By industry

Learn

Content

Lesson 3.1: Fetching simple data

The Fetch query

Projections

Selections

Entities and relations

Ternary relations

Footnotes