3.2 Using JSON to Store Vertex and Edge Properties

You can adopt a flexible schema approach in a property graph view by encoding the vertex and edge properties as a single JSON value. You can then map this to a property value in a property graph view.

The property graph schema graphs by nature provide schema flexibility. You can add arbitrary labels and properties to the graph without having to issue explicit schema updates (for example, through ALTER TABLE statement). Property graph views, on the other hand, do not provide such flexibility by nature since adding a new label requires adding a new vertex or edge table, and adding a new property requires adding a new column, both of which are schema update operations. However, through the use of JSON you can model schema flexibility on top of property graph views.

For example, consider the following graph which represent financial transactions between two Account vertices. The Account can be owned either by a Person or a Company.

Figure 3-1 Financial Transactions Graph

Description of "Figure 3-1 Financial Transactions Graph"

You can create a single table for storing all the vertices and another single table for storing all the edges, as shown:

CREATE TABLE fin_vertex_table (
  id NUMBER PRIMARY KEY,
  properties VARCHAR2(2000)
);
 
INSERT INTO fin_vertex_table VALUES ( 1, '{"type":"Person","name":"Nikita"}');
INSERT INTO fin_vertex_table VALUES ( 2, '{"type":"Person","name":"Camille"}');
INSERT INTO fin_vertex_table VALUES ( 3, '{"type":"Person","name":"Liam"}');
INSERT INTO fin_vertex_table VALUES ( 4, '{"type":"Company","name":"Oracle"}');
INSERT INTO fin_vertex_table VALUES ( 5, '{"type":"Account","number":10039}');
INSERT INTO fin_vertex_table VALUES ( 6, '{"type":"Account","number":2090}');
INSERT INTO fin_vertex_table VALUES ( 7, '{"type":"Account","number":8021}');
INSERT INTO fin_vertex_table VALUES ( 8, '{"type":"Account","number":1001}');
 
CREATE TABLE fin_edge_table (
  id NUMBER PRIMARY KEY,
  src NUMBER REFERENCES fin_vertex_table ( id ),
  dst NUMBER REFERENCES fin_vertex_table ( id ),
  properties VARCHAR2(2000)
);
 
INSERT INTO fin_edge_table VALUES ( 1, 7, 1, '{"type":"owner"}');
INSERT INTO fin_edge_table VALUES ( 2, 5, 2, '{"type":"owner"}');
INSERT INTO fin_edge_table VALUES ( 3, 6, 3, '{"type":"owner"}');
INSERT INTO fin_edge_table VALUES ( 4, 8, 4, '{"type":"owner"}');
INSERT INTO fin_edge_table VALUES ( 5, 2, 4, '{"type":"worksFor"}');
INSERT INTO fin_edge_table VALUES ( 6, 5, 7, '{"type":"transaction","amount":1000.00}');
INSERT INTO fin_edge_table VALUES ( 7, 7, 8, '{"type":"transaction","amount":1500.30}');
INSERT INTO fin_edge_table VALUES ( 8, 7, 8, '{"type":"transaction","amount":3000.70}');
INSERT INTO fin_edge_table VALUES ( 9, 8, 6, '{"type":"transaction","amount":9999.50}');
INSERT INTO fin_edge_table VALUES ( 10, 6, 5, '{"type":"transaction","amount":9900.00}');

As seen in the preceding code, each vertex and edge is represented by a single row in the respective tables. The first column is the unique key of the vertex or the edge. The second and third columns of the edge table are its source key and destination key respectively. The last column of the vertex and edge tables encodes all the properties as well as the labels in a JSON object. A JSON is an unordered set of name and value pairs. Here, you can use such pairs to encode the property names and their values as well as the label's value. In case of the label, you can choose an arbitrary name such as "type" or "label". In this example we use "type".

Because all the labels and properties of a vertex or an edge are encoded as a single JSON value, you do not need to update the schema when new labels or properties are added to the graph. Instead, you can add new labels and properties by inserting additional vertices and edges or by updating the JSON value in the underlying table through SQL.

The following two examples demonstrate how you can extract labels and property values from JSON objects for PGQL on RDBMS and PGQL on PGX respectively.

Example 3-2 Extracting JSON properties using JSON_VALUE (PGQL on RDBMS)

The following code creates a property graph view using the fin_vertex_table and fin_edge_table tables and executes a PGQL SELECT query:

PgqlStatement pgqlStmnt = pgqlConn.createStatement();
 
/* Create the property graph */
pgqlStmnt.execute(
  "CREATE PROPERTY GRAPH financial_transactions " +
  "  VERTEX TABLES ( " +
  "    fin_vertex_table PROPERTIES ( properties ) ) " +
  "  EDGE TABLES ( " +
  "    fin_edge_table " +
  "      SOURCE KEY ( src ) REFERENCES fin_vertex_table " +
  "      DESTINATION KEY ( dst ) REFERENCES fin_vertex_table " +
  "      PROPERTIES ( properties ) ) " +
  "  OPTIONS ( PG_VIEW )");
 
/* Set the name of the graph so that we can omit the ON clause from queries */
pgqlConn.setGraph("FINANCIAL_TRANSACTIONS");
 
/* PGQL query: find all outgoing transactions from account 8021. Output the
   transaction amount and the destination account number. */
PgqlResultSet rs = pgqlStmnt.executeQuery(
  "SELECT JSON_VALUE(trans.properties, '$.amount') AS transaction_amount, " +
  "       JSON_VALUE(account2.properties, '$.number') AS account_number " +
  "FROM MATCH (account1) -[trans]-> (account2) " +
  "WHERE JSON_VALUE(account1.properties, '$.number') = 8021 " +
  "  AND JSON_VALUE(trans.properties, '$.type') = 'transaction'");
 
rs.print();
rs.close();
pgqlStmnt.close();

In the preceding code, the CREATE PROPERTY GRAPH statement maps the JSON column into a property named "properties". This property will thus contain all the labels and properties of the vertex or the edge. The PGQL SELECT query extracts these labels and properties using JSON_VALUE.

For example, instead of account1.number = 8021, you must use JSON_VALUE(account1.properties, '$.number') = 8021. This causes the query to become a bit lengthier.

The output of the Java code is:

+-------------------------+
| AMOUNT | ACCOUNT_NUMBER |
+-------------------------+
| 1500.3 | 1001           |
| 3000.7 | 1001           |
+-------------------------+

Example 3-3 Using a UDF to extract a JSON property value (PGQL on PGX)

This example consists of two parts. The first part shows the creation of a UDF and the second part shows loading of the graph into the graph server (PGX) followed by the execution of a PGQL query using the UDF.

Since the Graph Server (PGX) does not have a built-in JSON_VALUE function like in PGQL on RDBMS, you can create a Java UDF instead.

Create the Java class (MyJsonUtils.java) that implements the UDF:

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
 
public class MyJsonUtils {
 
  private final static ObjectMapper mapper = new ObjectMapper();
 
  public static String get_prop(String json_string, String prop_name) throws JsonProcessingException {
    JsonNode node =  mapper.readTree(json_string);
    return node.path(prop_name).asText();
  }
}

Compile the class with the JARs from /opt/oracle/graph/pgx/server/lib/* added to the class path. This is because the library folder contains the necessary Jackson libraries that are required to parse the JSON.

mkdir ./target
javac -classpath .:/opt/oracle/graph/pgx/server/lib/* -d ./target *.java
cd target
jar cvf MyJsonUtils.jar *

Using the following UDF JSON configuration file (my_udfs.json), you can now register the Java UDF on the graph server (PGX) by following step-3 to step-6 in User-Defined Functions (UDFs) in PGX:

{
  "user_defined_functions": [
    {
      "namespace": "my",
      "function_name": "get_prop",
      "language": "java",
      "implementation_reference": "MyJsonUtils",
      "return_type": "string",
      "arguments": [
        {
          "name": "json_string",
          "type": "string"
        },
        {
          "name": "prop_name",
          "type": "string"
        }
      ]
    }
  ]
}

On implementing the UDF for extracting property values from the JSON, you can now load the graph into the Graph Server (PGX) and issue a PGQL query:

/* Load the graph into the Graph Server (PGX) */
ServerInstance instance = GraphServer.getInstance("http://localhost:7007", username, password.toCharArray());
session = instance.createSession("my-session");
PgxGraph g = session.readGraphByName("FINANCIAL_TRANSACTIONS", GraphSource.PG_VIEW);
 
/* PGQL query: find all shortest paths from account 10039 to account 2090 following only outgoing transaction
   edges. Output the list of transaction amounts along each path as well as the total amount of the transactions
   along each path. */
g.queryPgql(
  "  SELECT LISTAGG(my.get_prop(e.properties, 'amount'), ' + ') || ' = ' AS amounts_along_path, " +
  "         SUM(CAST(my.get_prop(e.properties, 'amount') AS DOUBLE)) AS total_amount " +
  "    FROM MATCH ALL SHORTEST (a) (-[e]-> WHERE my.get_prop(e.properties, 'type') = 'transaction')* (b) " +
  "   WHERE my.get_prop(a.properties, 'number') = '10039' AND " +
  "         my.get_prop(b.properties, 'number') = '2090' " +
  "ORDER BY total_amount").print().close();

The output of the Java code is:

+--------------------------------------------+
| amounts_along_path          | total_amount |
+--------------------------------------------+
| 1000.0 + 1500.3 + 9999.5 =  | 12499.8      |
| 1000.0 + 3000.7 + 9999.5 =  | 14000.2      |
+--------------------------------------------+