Tutorial: Cross-Model Queries

Polypheny is a powerful tool that allows you to manage and query data stored in different models seamlessly. This tutorial will walk you through creating a simple toy example to illustrate how to perform cross-model queries in Polypheny.

Introduction

This tutorial will focus on the logical data model, leaving out the details of physical storage. Polypheny allows you to perform queries across different storage models seamlessly, regardless of where the data is stored. While the underlying storage architecture doesn’t impact the ability to perform queries, using optimized stores can significantly improve performance.

In this tutorial, we will create a simple toy example of an e-commerce data management scenario. We will manage product data in a document model, user data in a graph model, and order data in a relational model.

Setting Up Namespaces

First, create three namespaces: “crm” for graph data, “shop” for document data, and “sales” for relational data. This can be done using the UI or with the following SQL query:

CREATE GRAPH NAMESPACE "crm";
CREATE DOCUMENT NAMESPACE "shop";
CREATE RELATIONAL NAMESPACE "sales";

Creating Entities and Adding Data

Next, let’s create a “products” collection in the “shop” namespace. We can do this either using the UI or using an MQL query:

use shop;
db.createCollection("products");
db.products.insertMany([
   { name: "Product 1", price: 19.99, details: { weight: "1kg", color: "Red" } },
   { name: "Product 2", price: 22.50, details: { weight: "2kg", color: "Blue" } }
]);

Next, we create the order table using a SQL query and insert some data:

CREATE TABLE sales.orders (
    order_id INTEGER PRIMARY KEY,
    user_id INTEGER,
    product_name VARCHAR(255),
    quantity INTEGER
);

INSERT INTO sales.orders (order_id, user_id, product_name, quantity) VALUES
(1, 1, 'Product 1', 2),
(2, 2, 'Product 2', 1);

Last but not least, we populate the user namespace with some graph data using Cypher:

use crm;
CREATE (user1:customer {id: 1, name: 'Alice'}),
       (user2:customer {id: 2, name: 'Bob'}),
       (user1)-[:KNOWS]->(user2);

Simple Cross-Model Queries

Now, let’s perform queries that span across different models. We call it a cross-model query if at least two data models are involved. This is the case if an entity is accessed using a query language based on a different data model than the entity.

Query Document Data Using SQL

Retrieve product information using SQL from the document model:

SELECT 
  JSON_VALUE(d, 'lax $.name') AS name,
  JSON_VALUE(d, 'lax $.price') AS price,
  JSON_VALUE(d, 'lax $.details.weight') AS weight,
  JSON_VALUE(d, 'lax $.details.color') AS color
FROM shop.products;

Query Graph Data Using SQL

Retrieve user information using SQL from the graph model:

SELECT JSON_VALUE(properties, 'lax $.name') AS user_name
FROM crm.customer;

Query Relational Data Using Cypher

Retrieve order information using Cypher from the relational model:

use sales;
MATCH (o:orders)
RETURN o.order_id, o.user_id, o.product_name, o.quantity;

To view as graph, return the whole graph instead of projecting:

use sales;
MATCH (o:orders)
RETURN o;

Query Document Data Using Cypher

Retrieve product details using Cypher from the document model:

use shop;
MATCH (p:products)
RETURN p.name, p.price, p.details;

Query Graph Data Using MongoDB

Retrieve user relationships using MongoDB query language from the graph model:

use crm;
db.customer.find({});

Combine From Different Namespaces

Polypheny also supports combining (e.g., joining) data maintained according to different data models.

Combining Relational and Document Data

If we want to find orders along with product details maintained in the document model:

SELECT 
  o.order_id, 
  o.user_id, 
  o.product_name, 
  o.quantity, 
  JSON_VALUE(p.d, 'lax $.price') AS price
FROM sales.orders o
JOIN shop.products p
ON o.product_name = JSON_VALUE(p.d, 'lax $.name');

This query combines order information from the relational model with product information from the document model.

Combining Relational and Graph Data

We can also join with the customers maintained in a graph. This is handy if we want to find the customers who placed orders along with their order details:

SELECT 
  o.order_id,
  JSON_VALUE(c.properties, 'lax $.name') AS customer_name,
  o.product_name,
  o.quantity
FROM sales.orders o
JOIN crm.customer c
ON o.user_id = CAST(JSON_VALUE(c.properties, 'lax $.id') AS INTEGER)

Combining Data From All Three Namespaces

Now let’s combine data from all three namespaces. We want to find user names, their orders, and product details:

SELECT 
  JSON_VALUE(c.properties, 'lax $.name') AS customer_name, 
  o.order_id, 
  o.product_name,
  o.quantity, 
  JSON_VALUE(p.d, 'lax $.price') AS price
FROM sales.orders o
JOIN crm.customer c
ON o.user_id = CAST(JSON_VALUE(c.properties, 'lax $.id') AS INTEGER)
JOIN shop.products p
ON o.product_name = JSON_VALUE(p.d, 'lax $.name');

This comprehensive query integrates user information from the graph model, order information from the relational model, and product details from the document model, demonstrating the power of Polypheny’s cross-model query capabilities.

Previous

Notebooks

Next

Schema Model