Property graphs: elements, labels and properties

A property graph consists of three types of "things" in it: elements, labels and properties. 

Elements are nodes or edges in the graphs. They form the basic structure of a graph. An edge connects two nodes. Two nodes may be connected by multiple edges corresponding to different relationships between them.

Labels classify the elements. An element may belong to multiple classes and thus have multiple labels.

Properties are key-value pairs providing more information about an element. All the elements with the same label expose same set of keys or properties. A property of a given element may be exposed through multiple labels associated with that element.

Let's use the diagram here to understand the concepts better. There are three elements: N1, N2, the vertexes and an edge connecting them, labels L1 to L4, properties P1 to P7. The arrows connecting a label to an property indicates that that label exposes that property. E.g. label L3 exposes properties P2, P3, P5. Property P1 is exposed by both L1 and L2. An arrow between an element and a label indicates that that label is associated with that element. N1 has labels L1 and L2 whereas the edge has just one label L4. The properties that are associated with (and are exposed by) an element are decided by the labels associated with it. E.g. the properties P1, P2 and P4, which are union of properties associated with labels L1 and L2, are exposed by element N1. P1 has the same value v1 irrespective of which label is considered for this association. E.g height of a person will not change whether that person is classified as a teacher, businessman or a plumber. Similarly notice that the edge exposes properties P6 and P7 since it is labelled as L4.

SQL/PGQ's path pattern specification language allows to specify paths in terms of labels ultimately exposing the properties of individual paths that obey that patterns. E.g. (a IS L1 | L2)-[]->(b IS L3) COLUMNS (a.P3) will returns values of property P2 of all the nodes with labels L1 or L2. If you notice that N1 and N2 are the elements associated with either L1 or L2 or both. But N1 does not expose property P3. Hence we might expect that the above query would return an error. But instead the standard specified that it should report NULL, quite inline with the spirit of SQL NULL which means unknown.

The way I see it, a property can not exist without at least one label exposing it. A label can not exist without being associated with at least an element. But once defined, they have quite an independent existence.

DBaaG with SQL/PGQ

For those who have studied ERD-lore, it's not new that a relational database is very much like a graph. But it has taken SQL, more than 30 years since it became a standard and almost half a century since its inception to incorporate construct that will allow a DataBase to be treated as a Graph, DBaaG. This is surprising given that SQL was developed as language for relational databases which are modeled using ER diagrams. Better late than never. SQL/PGQ has arrived as 16th part of SQL:2023.

Entity Relationship Diagram, ERD in short, is a tool to model and visualize a database as entity types (which classify the things of interest) and relationships that can exist between them. Entity types and the relationships both map to relations in a Relational DataBase Management System (RDBMS in short). The rows in the relations represent entities (instances of entity types) and relationship between entities respectively. Fig. 1 below shows an ERD for a hypothetical shop.

This diagram very much looks like a graph with entity types represented as nodes and relationships represented by edges. That's exactly what SQL/PGQ is about. It adds language constructs to SQL to present underlying database as a "Property Graph". For example, property graph definition corresponding to the above ERD would look like

CREATE PROPERTY GRAPH shop

VERTEX TABLES (

    CreditCard label Payment,

    BankAccount label Payment,

    Person label Customer,

    Company label Customer,

    Trust label Customer,

    Wishlist label ProdLink,

    Order label ProdLink,

    Product)

EDGE TABLES (

    CCOwns label Owns

    BAHolds lable Owns,

    CustOrders label CustLink,

    CustWishlist label CustLink,

    CompanyOrders label CustLink,

    CompanyWishlist label CustLink,

    TrustOrders label CustLink,

    TrustWishlist label CustLink,

    OrderCCPayment label OrderPayment,

    OrderBAPayment label OrderPayment,

    OrderItems label ItemLink,

    WishlistItems label ItemLink);

 

Clever readers may have noticed that some of the entity types have some commonality. CreditCard and BankAccount are both Payment methods. Person, Company and Trust all can be considered as "Customers" by the shop. In a graph entities with commonalities will be represented by visual annotations like colors in Fig. 2. SQL/PGQ chooses to represents them by labels. Columns of the underlying tables are exposed through properties of labels. Traditionally the labels may be implemented as table inheritance or through tables abstracting commonalities. But it may not be necessary anymore.

Augmented with query constructs in SQL/PGQ they make it easy to write queries, especially analytical. Imagine a query to find all the products paid via credit card. There will be tons of JOIN and UNIONs over those joins. That's almost like "implementing" a logic in SQL. You would ask which parts do I JOIN before UNION, and which parts do I UNION before JOIN and so on. That's against the "imperative" spirit of SQL which should allow you to tell "what" you want and leave "how" for the DBMS to figure out. With SQL/PGQ you tell the DBaaG which paths in the graph to traverse. How to traverse them is the system's responsibility. So the SQL/PGQ query looks like below. Much simpler than joins and unions. In fact, it allows me not to mention edge tables at all in the query.

SELECT distinct name FROM

    GRAPH_TABLE (shop

                MATCHES

                   (o IS Orders)->(py IS Payment WHERE py.type = 'CC')<-(c IS Customer)->(o IS Order)->(p is Product)

                COLUMNS (p.name));

 

I must note that the query looks more like a mathematical equation than SQL which till now followed natural language syntax. But well, there it is.

What those () mean? What about various literals in it? How to specify properties? I am sure I have roused more questions than those answered here. I plan to write more about it in future. This blog has gone longer than I initially intended it to be, but I hope it has aroused your interest in SQL/PGQ nonetheless.

Oh! but before I end, please note that we are working on implementing SQL/PGQ in PostgreSQL. If you are interested and want to contribute, please follow and respond on pgsql-hackers thread.