REST Query Interface

On this page

Introduction
API Base URL
Authentication
Response Format
Error Handling
Basic CRUD Operations
Filter
Sort
Aggregate
Project
Join
Group
Limiting and Offsetting Results

This document provides a detailed guide to using the REST query interface of Polypheny. The interface provides an easy way for external applications to interact with Polypheny using HTTP methods.

Introduction

REST (Representational State Transfer) is an architectural style for distributed systems, particularly web services. A RESTful interface allows for interaction with resources (like database tables or objects) through standard HTTP methods, such as GET (retrieve), POST (create), PUT/PATCH (update), and DELETE (delete). REST interfaces are useful because:

Platform Independence: Clients can be written in any language and run on any platform that supports HTTP.
Statelessness: Each request from a client contains all the information needed to process the request, making scaling easier.
Cacheability: Responses can be cached, enhancing performance.
Layered System: Allows for more modular architecture, with components interacting in a layered manner.

In the context of Polypheny, the REST interface offers a seamless way to interact with the database using the familiar HTTP methods. It abstracts away the complexities of direct database communication, providing a more straightforward way to execute queries and manage data.

The REST interface has been designed with relational applications in mind. Support for namespaces of type document and graph is limited. Furthermore, the interface is intended for simple applications. Consider using the HTTP or Prism Interface for more sophisticated applications.

API Base URL

The base URL for all API endpoints is:

{protocol}://{host}:{port}/restapi/v1/res/{namespace}.{entity}

Where:

{protocol}: The protocol used, typically http.
{host}: The host address, typically 127.0.0.1 for local installations.
{port}: The port on which the REST Interface is listing, default is 8089.
{namespace}: The name of the namespace
{entity}: The name of the entity (e.g., table)

Authentication

All requests to the REST API must be authenticated using basic authentication. Please use the username pa and an empty password.

request.basicAuth("pa", "");

Response Format

The server responds with a JSON object. A typical response object contains the following:

{
  "result": [
    {
      "column_name": "value",
      ...
    }
  ],
  "size": 1
}

Error Handling

If the server returns a non-success status code, an exception is raised with an appropriate error message.

Basic CRUD Operations

CREATE (Insert Data)

To insert data into a table, use the POST method.

Payload:

{
  "data": [
    {
      "namespace.entity.column": "value",
      ...
    }
  ]
}

Examples:

Java

import kong.unirest.Unirest;
import com.google.gson.JsonObject;
import com.google.gson.JsonPrimitive;

public void insertData() {
    JsonObject row = new JsonObject();
    row.add("public.users.name", new JsonPrimitive("John Doe"));
    row.add("public.users.age", new JsonPrimitive(25));

    String url = "http://127.0.0.1:8089/restapi/v1/res/public.users";
    Unirest.post(url)
        .basicAuth("pa", "")
        .header("Content-ExpressionType", "application/json")
        .body(row)
        .asString();
}

Bash

#!/bin/bash

# ------------------------------
# Configuration
# ------------------------------

HOST="127.0.0.1"
PORT="8089"

USERNAME="pa"
PASSWORD=""

ENDPOINT="/restapi/v1/res/public.user"

# Data to be sent to the server
USER_DATA=(
    "public.user.name=John Doe"
    "public.user.age=25"
)

# Convert data array to JSON format
json_data() {
    local data
    for item in "${USER_DATA[@]}"; do
        key="${item%=*}"
        value="${item#*=}"
        data+="\"$key\": \"$value\","
    done
    echo "{ \"data\": [ { ${data%,} } ] }"
}

# ------------------------------
# Main Script Execution
# ------------------------------

URL="http://$HOST:$PORT$ENDPOINT"

# Send POST request using curl
curl -X POST "$URL" \
     -u "$USERNAME:$PASSWORD" \
     -H "Content-Type: application/json" \
     -d "$(json_data)"

READ (Retrieve Data)

To retrieve data from a table or view, use the GET method.

Query Parameters:

<namespace>.<entity>.<alias_or_column>=<operation><literal_value>

Examples:

Retrieving rows from the table public.users where name is John Doe:

Java

import kong.unirest.Unirest;

public void fetchData() {
    String filterCondition = "public.users.name==John Doe";

    String url = "http://127.0.0.1:8089/restapi/v1/res/public.users";
    String response = Unirest.get(url)
        .basicAuth("pa", "")
        .queryString(filterCondition)
        .asString()
        .getBody();
}

Bash

#!/bin/bash

# ------------------------------
# Configuration
# ------------------------------

HOST="127.0.0.1"
PORT="8089"
USERNAME="pa"
PASSWORD=""
ENDPOINT="/restapi/v1/res/public.users"

# Filter condition components
COLUMN="public.users.name"
OPERATOR="="
VALUE="John Doe"

# Assemble the filter condition
FILTER_CONDITION="${COLUMN}=${OPERATOR}${VALUE}"

# ------------------------------
# Main Script Execution
# ------------------------------

URL="http://$HOST:$PORT$ENDPOINT"

# Send GET request using curl
curl -X GET "$URL" \
     -u "$USERNAME:$PASSWORD" \
     -G \
     --data-urlencode "$FILTER_CONDITION"

UPDATE (Modify Data)

To update data in a table, use the PATCH method.

Payload:

{
  "data": [
    {
      "namespace.entity.column": "new_value",
      ...
    }
  ]
}

Query Parameters:

<namespace>.<entity>.<alias_or_column>=<operation><literal_value>

Examples:

Updating the value of age to 26 in the table public.users where name is John Doe:

Java

import kong.unirest.Unirest;
import com.google.gson.JsonObject;

public void updateData() {
    // Data to update
    JsonObject dataToUpdate = new JsonObject();
    dataToUpdate.addProperty("public.users.age", 26);

    String filterCondition = "public.users.name==John Doe";

    String url = "http://127.0.0.1:8089/restapi/v1/res/public.users";
    Unirest.patch(url)
        .basicAuth("pa", "")
        .header("Content-Type", "application/json")
        .queryString(filterCondition)
        .body(dataToUpdate.toString())
        .asString();
}

Bash

DELETE (Remove Data)

To delete data from a table, use the DELETE method.

Query Parameters:

<namespace>.<entity>.<alias_or_column>=<operation><literal_value>

Examples:

Deleting rows from the table public.users where name is John Doe:

Java

import kong.unirest.Unirest;

public void deleteData() {
    String filterCondition = "public.users.name==John Doe";

    String url = "http://127.0.0.1:8089/restapi/v1/res/public.users";
    Unirest.delete(url)
        .basicAuth("pa", "")
        .queryString(filterCondition)
        .asString();
}

Bash

#!/bin/bash

# ------------------------------
# Configuration
# ------------------------------

HOST="127.0.0.1"
PORT="8089"
USERNAME="pa"
PASSWORD=""
ENDPOINT="/restapi/v1/res/public.users"

# Filter condition components
FILTER_COLUMN="public.users.name"
FILTER_VALUE="John Doe"
FILTER_OPERATOR="="
FILTER_CONDITION="${FILTER_COLUMN}=${FILTER_OPERATOR}${FILTER_VALUE}"

# ------------------------------
# Main Script Execution
# ------------------------------

URL="http://$HOST:$PORT$ENDPOINT"

# Send DELETE request using curl
curl -X DELETE "$URL" \
     -u "$USERNAME:$PASSWORD" \
     --data-urlencode "$FILTER_CONDITION"

Filter

Logical Operators:

AND: Combines two or more conditions and returns true if all conditions are true.
OR: Combines two or more conditions and returns true if at least one condition is true.

Comparison Operators:

EQ: Equal to.
NEQ: Not equal to.
GT: Greater than.
LT: Less than.
GTE: Greater than or equal to.
LTE: Less than or equal to.

Special Operators:

IN: Checks if a value is within a set of values.
LIKE: Checks if a value matches a pattern (with wildcards).

Syntax:

Filters should be provided as a JSON object.
Each filter condition should be an object with the following properties:
- column: The name of the column to filter on.
- operator: The operator to use (from the list above).
- value: The value to compare against.
Multiple conditions can be combined using the logical operators.

Usage:

To filter rows where the column “name” is equal to “John”:

{
  "column": "name",
  "operator": "EQ",
  "value": "John"
}

To filter rows where the column “age” is greater than 25 and the column “city” is “New York”:

{
  "AND": [
    {
      "column": "age",
      "operator": "GT",
      "value": 25
    },
    {
      "column": "city",
      "operator": "EQ",
      "value": "New York"
    }
  ]
}

Examples:

Filtering rows from the table public.users where the column age is greater than 25 and the column city is “New York”:

Java

import kong.unirest.Unirest;
import com.google.gson.JsonObject;
import com.google.gson.JsonArray;

public void filterData() {
    // Constructing the filter conditions
    JsonObject ageCondition = new JsonObject();
    ageCondition.addProperty("column", "public.users.age");
    ageCondition.addProperty("operator", "GT");
    ageCondition.addProperty("value", 25);

    JsonObject cityCondition = new JsonObject();
    cityCondition.addProperty("column", "public.users.city");
    cityCondition.addProperty("operator", "EQ");
    cityCondition.addProperty("value", "New York");

    JsonArray combinedConditions = new JsonArray();
    combinedConditions.add(ageCondition);
    combinedConditions.add(cityCondition);

    JsonObject filter = new JsonObject();
    filter.add("AND", combinedConditions);

    // Making the request
    String url = "http://127.0.0.1:8089/restapi/v1/res/public.users";
    String response = Unirest.get(url)
        .basicAuth("pa", "")
        .queryString("filter", filter.toString())
        .asString()
        .getBody();

    System.out.println(response);
}

Sort

The REST API provides a sorting option that allows users to sort the results of their queries based on specified columns. The sorting option is implemented using the sort parameter in the request.

Syntax:

?sort=<columnName>:<sortOrder>

<columnName>: The name of the column by which you want to sort the results.
<sortOrder>: The order in which you want to sort the results. It can be either asc for ascending order or desc for descending order.

Multiple columns can be specified for sorting by separating them with a comma.

Usage:

?sort=name:asc,age:desc

This will sort the results first by the name column in ascending order and then by the age column in descending order.

Examples:

Java

import kong.unirest.Unirest;

public void fetchSortedData() {
    // Define the sorting conditions
    String sortConditions = "public.users.name:asc,public.users.age:desc";

    // Making the request
    String url = "http://127.0.0.1:8089/restapi/v1/res/public.users";
    String response = Unirest.get(url)
        .basicAuth("pa", "")
        .queryString("sort", sortConditions)
        .asString()
        .getBody();

    System.out.println(response);
}

Aggregate

The aggregate option allows you to perform aggregation operations on the data. The structure of the aggregate option is as follows:

type: Specifies the type of aggregation operation to be performed. Supported values include:
- COUNT
- SUM
- AVG
- MIN
- MAX
column: Specifies the column on which the aggregation operation should be performed.
as: (Optional) Specifies an alias for the resulting aggregated column.

Usage:

{
  "aggregate": [
    {
      "type": "SUM",
      "column": "price",
      "as": "total_price"
    }
  ]
}

In the above example, the SUM aggregation operation will be performed on the price column, and the result will be aliased as total_price.

Example (Java):

import kong.unirest.Unirest;

public void fetchAggregatedData() {
    // Define the aggregate conditions
    String aggregateConditions = "type:SUM,column:public.products.price,as:total_price";

    // Making the request
    String url = "http://127.0.0.1:8089/restapi/v1/res/public.products";
    String response = Unirest.get(url)
        .basicAuth("pa", "")
        .queryString("aggregate", aggregateConditions)
        .asString()
        .getBody();

    System.out.println(response);
}

Project

The project option allows you to select specific columns from the data. The structure of the project option is as follows:

column: Specifies the name of the column to be selected.
as: (Optional) Specifies an alias for the selected column.

Usage:

{
  "project": [
    {
      "column": "product_name",
      "as": "name"
    },
    {
      "column": "product_id"
    }
  ]
}

In the above example, the product_name column will be selected and aliased as name, and the product_id column will be selected without any alias.

Examples:

Java

import kong.unirest.Unirest;

public void fetchProjectedData() {
    // Define the project conditions
    String projectConditions = "column:public.products.product_name,as:name;column:public.products.product_id";

    // Making the request
    String url = "http://127.0.0.1:8089/restapi/v1/res/public.products";
    String response = Unirest.get(url)
        .basicAuth("pa", "")
        .queryString("project", projectConditions)
        .asString()
        .getBody();

    System.out.println(response);
}

Join

The REST interface allows you to perform join operations between tables. Joining tables combines rows from two or more tables based on a related column between them. This enables you to query data from multiple tables as one set.

Syntax:

To perform a join operation, you need to specify the tables you want to join and the condition for the join. The general syntax for joining is:

{protocol}://{host}:{port}/restapi/v1/res/{namespace}.{entity}?join={namespace2}.{entity2}&on={namespace}.{entity}.{column}={namespace2}.{entity2}.{column2}

Where:

{namespace2} and {entity2} represent the second table you want to join with.
{column} and {column2} are the columns on which the join condition is based.

Types of Joins:

The REST interface supports various types of joins, including:

Inner Join: Returns records that have matching values in both tables.
Left (Outer) Join: Returns all records from the left table, and the matched records from the right table.
Right (Outer) Join: Returns all records from the right table, and the matched records from the left table.
Full (Outer) Join: Returns all records when there is a match in either the left or the right table.

The type of join can be specified using the type parameter in the URL.

Examples: Joining the public.users table with the public.orders table on the id column:

Java

import kong.unirest.Unirest;

public void joinTables() {
    String url = "http://127.0.0.1:8089/restapi/v1/res/public.users";
    String response = Unirest.get(url)
        .basicAuth("pa", "")
        .queryString("join", "public.orders")
        .queryString("on", "public.users.id=public.orders.userId")
        .asString()
        .getBody();
}

In this example, we are performing an inner join by default. To specify a different type of join, you can add the type parameter to the query string.

Group

The REST interface allows you to group data based on one or more columns. Grouping data is particularly useful when you want to aggregate data in specific categories.

Syntax:

To perform a grouping operation, you need to specify the columns by which you want to group the data. The general syntax for grouping is:

{protocol}://{host}:{port}/restapi/v1/res/{namespace}.{entity}?groupby={namespace}.{entity}.{column1},{namespace}.{entity}.{column2},...

Where:

{column1}, {column2}, … are the columns by which you want to group the data.

Using Grouping with Aggregation

Grouping is often used in conjunction with aggregation to summarize data. For instance, you can group data by a specific column and then calculate the sum, average, or count of another column within each group.

Examples:

Grouping the public.sales table by the product column and calculating the total quantity sold for each product:

Java

import kong.unirest.Unirest;

public void groupData() {
    String url = "http://127.0.0.1:8089/restapi/v1/res/public.sales";
    String response = Unirest.get(url)
        .basicAuth("pa", "")
        .queryString("groupby", "public.sales.product")
        .queryString("aggregate", "type:SUM,column:public.sales.quantity,as:total_quantity")
        .asString()
        .getBody();

    System.out.println(response);
}

In this example, the data from the sales table is grouped by the product column. For each product, the total quantity sold is calculated and returned as total_quantity.

Limiting and Offsetting Results

The REST interface provides functionality to limit the number of results returned by a query and to offset the starting point of the results. This is particularly useful for implementing pagination in applications.

Limiting

To limit the number of results returned by a query, use the limit parameter.

Syntax:

{protocol}://{host}:{port}/restapi/v1/res/{namespace}.{entity}?limit={number}

Where:

{number} is the maximum number of results you want to retrieve.

Offsetting

To skip a specific number of results before starting to return the results, use the offset parameter.

Syntax:

{protocol}://{host}:{port}/restapi/v1/res/{namespace}.{entity}?offset={number}

Where:

{number} is the number of results to skip before returning the remaining results.

Using Limiting and Offsetting Together

You can use both limit and offset together to implement pagination.

Syntax:

{protocol}://{host}:{port}/restapi/v1/res/{namespace}.{entity}?limit={limitNumber}&offset={offsetNumber}

Where:

{limitNumber} is the maximum number of results you want to retrieve.
{offsetNumber} is the number of results to skip.

Example (Java):

Retrieving the next 10 products from the public.products table after skipping the first 50:

import kong.unirest.Unirest;

public void fetchPaginatedData() {
    String url = "http://127.0.0.1:8089/restapi/v1/res/public.products";
    String response = Unirest.get(url)
        .basicAuth("pa", "")
        .queryString("limit", 10)
        .queryString("offset", 50)
        .asString()
        .getBody();

    System.out.println(response);
}

In this example, the first 50 products are skipped, and the next 10 products are retrieved from the products table.

Development

Overview

Quick Search Results

Introduction

API Base URL

Authentication

Response Format

Error Handling

Basic CRUD Operations

CREATE (Insert Data)

READ (Retrieve Data)

UPDATE (Modify Data)

DELETE (Remove Data)

Filter

Sort

Aggregate

Project

Join

Group

Using Grouping with Aggregation

Limiting and Offsetting Results

Limiting

Offsetting

Using Limiting and Offsetting Together