4 SQL DDL Statements for Property Graphs

You can create, revalidate, and drop SQL property graphs using SQL data definition language (DDL) statements.

4.1 Creating a SQL Property Graph

Using the CREATE PROPERTY GRAPH DDL statement, you can create a property graph object directly in an Oracle Database.

Example 4-1 Creating a SQL Property Graph Using the CREATE PROPERTY GRAPH DDL Statement

This example creates a SQL property graph, students_graph, using persons, university, friends, and student_of as the underlying database tables for the graph.

In order to run this example, ensure the following:

  1. Set up the sample tables in the database as explained in Setting Up Sample Data in the Database.
  2. See Granting System and Object Privileges for SQL Property Graphs to ensure you have the required privileges to create a SQL property graph.

The following diagram illustrates the students_graph:

The corresponding SQL propery graph DDL statement is as shown:

CREATE PROPERTY GRAPH students_graph
  VERTEX TABLES (
    persons KEY (person_id)
      LABEL person
        PROPERTIES (person_id, name, birthdate AS dob)
      LABEL person_ht
        PROPERTIES (height),
    university KEY (id)
  )
  EDGE TABLES (
    friends
      KEY (friendship_id)
      SOURCE KEY (person_a) REFERENCES persons(person_id)
      DESTINATION KEY (person_b) REFERENCES persons(person_id)
      PROPERTIES (friendship_id, meeting_date),
    student_of
      SOURCE KEY (s_person_id) REFERENCES persons(person_id)
      DESTINATION KEY (s_univ_id) REFERENCES university(id)
      PROPERTIES (subject)
  );

On execution, the preceding example creates a SQL property graph object that uses the tables in your schema to define its graph element tables. Note that the creation of the new SQL property graph object, results only in the storage of the property graph metadata, and there is no copying of data from the underlying database objects into the graph element tables. This implies that when querying a SQL property graph, all GRAPH_TABLE queries are performed on the current graph data in the database. You may also specify another schema to contain the SQL property graph provided that you have sufficient privileges.

The graph definition in the example creates a graph that comprises:
  • Two vertex graph element tables:
    • persons: The table has an explicitly defined unique key, person_id, and it is associated with two labels:
      • person: This label exposes person_id, name and birthdate as properties.
      • person_ht: This label exposes only the height property.
    • university: The label for the table is implicitly inferred and by default all visible columns of the underlying database table are exposed as properties.
  • Two edge graph element tables:
    • friends: The edge table references persons as the underlying database table for both the source and destination vertex tables. The source and destination keys (person_a and person_b) for the edge table correspond to the unique key of the source and destination vertex tables respectively. The label for the edge table is automatically inferred from the name of the graph element table (friends, in this case) and exposes friendship_id and meeting_date as properties.
    • student_of: The edge table references persons and university as the underlying database tables for the source and destination vertex tables respectively. The source and destination keys (s_person_id and s_univ_id) for the edge table correspond to the unique key of the source and destination vertex tables respectively. The label for the edge table is automatically inferred from the name of the graph element table (student_of, in this case) and exposes subject as the property.
It is important to note that once a SQL property graph is created, you cannot alter the graph definition. However, you can redefine a SQL property graph using the OR REPLACE clause in the CREATE PROPERTY GRAPH DDL statement. You can use this clause to change the definition of an existing SQL property graph without dropping, re-creating, and regranting object privileges that were earlier granted on it.

See Also:

CREATE PROPERTY GRAPH in Oracle Database SQL Language Reference

The following sections explain more on the concepts of the graph element tables, keys, labels and properties:

4.1.1 About Vertex and Edge Graph Element Tables

The vertices and edges of a SQL property graph defined from the underlying database objects are stored in the graph element tables.

A graph element table can either be a vertex table or an edge table.

Refer to the graph definition in Example 4-1 to easily understand the following sections:

Vertex graph element table

  • A vertex table is defined using the VERTEX TABLES clause.
  • Each row in a vertex table corresponds to a vertex of the graph.
  • A vertex graph element table has a name that is independent from the name of the underlying database object.
  • By default, the name of the vertex graph element table is the same as the name of the underlying database object.
  • A vertex table name must be unique for a graph. In case you want to define a SQL property graph with multiple graph element tables from the same database object, then you must specify an alternate graph element table name using the AS clause.

Edge graph element table

  • An edge table is defined using the EDGE TABLES clause.
  • It specifies a direct relationship between the source vertex table and the destination vertex table using the SOURCE and DESTINATION keywords that REFERENCES the respective vertex tables.
  • Each row in an edge table corresponds to an edge of the graph.
  • An edge graph element table has a name that is independent from the name of the underlying database object.
  • By default, the name of the edge graph element table is the same as the name of the underlying database object.
  • The edge table name must be unique for a graph. An edge table name cannot be shared with a vertex table or another edge table.

4.1.2 About Vertex and Edge Table Keys

Each vertex and edge table used in a SQL property graph definition must have a key in order to identify a unique vertex or an edge in a SQL property graph.

The key is defined from one or more columns of the underlying table. The key may be implicitly inferred based on an existing primary key or a unique constraint defined on the underlying table, or explicitly defined. The key should be unique.

However, note that the uniqueness constraint for the key column is required if you create the graph in ENFORCED MODE. Otherwise, you can create the graph in TRUSTED MODE using key columns that do not have a uniqueness constraint. See Using Graph Options to Create SQL Property Graphs for more information on the different modes that can be applied during graph creation.

Vertex or edge table keys can be defined for any of the following built-in data type columns:

  • VARCHAR2
  • NVARCHAR2
  • NUMBER
  • BINARY_FLOAT
  • BINARY_DOUBLE
  • CHAR
  • NCHAR
  • DATE
  • INTERVAL (both YEAR TO MONTH and DAY TO SECOND)
  • TIMESTAMP

Note that the TIMESTAMP WITH TIME ZONE data type is not supported.

Refer to the SQL property graph definition in Example 4-1 to easily understand the following sections:

Vertex Table Key

  • By default, the key for a vertex table is automatically identified from a single PRIMARY KEY or UNIQUE key constraint on the underlying database object. If both exist, then the PRIMARY KEY constraint takes precedence over the UNIQUE key constraint.
  • If the vertex table key is automatically inferred based on a single UNIQUE key, then the set of columns in that UNIQUE key must also be NOT NULL.
  • If the underlying database object does not contain a unique constraint to enforce uniqueness, then you must explicitly define the KEY subclause in the VERTEX TABLES clause, to identify the columns that define a unique key for the vertex table. Note that the column names must match the column names of the underlying database object.
  • Composite vertex table keys are also supported.

Edge Table Key

  • By default, the key for an edge table is automatically identified from a single PRIMARY KEY or UNIQUE key constraint on the underlying database object. If both exist, then the PRIMARY KEY constraint takes precedence over the UNIQUE key constraint.
  • If the edge table key is automatically inferred based on a single UNIQUE key, then the set of columns in that UNIQUE key must also be NOT NULL.
  • If the underlying database object does not contain a unique constraint to enforce uniqueness, then you must explicitly define the KEY subclause in the EDGE TABLES clause, to identify the columns that define a unique key for the edge table. Note that the column names must match the column names of the underlying database object.
  • By default, the SOURCE and DESTINATION table keys are automatically obtained from a single FOREIGN KEY constraint between the edge table and the underlying source and destination tables respectively.
  • However, you must explicitly specify the KEY subclause for the SOURCE and DESTINATION vertex tables, if any of the following applies:
    • There is no FOREIGN KEY constraint between the edge and the referenced vertex tables.
    • There are multiple FOREIGN KEY constraints between the edge and the referenced vertex tables.
    • The underlying database objects for the edge table and its source and destination vertex tables are materialized views or external tables.

Note:

All restrictions that apply for primary key constraints on a database object also apply on vertex and edge table keys.

4.1.3 About Labels and Properties

Labels can be associated to one or more graph element tables and they enrich the graph definition. A label can be defined with or without properties.

You can optionally define LABELS and PROPERTIES for the vertex and edge tables in your graph. When not specified, the graph element tables are automatically assigned a label with the name of the graph element table, and all visible columns are exposed as properties, using the column name as property name.

Refer to the SQL property graph definition in Example 4-1 to easily understand the following sections:

Labels

  • By default, the vertex and edge tables are automatically assigned a label with the name of the respective graph element tables.
  • The DEFAULT LABEL subclause can also be used to explicitly apply the preceding rule.
  • You can explicitly assign a new label name to a vertex or an edge graph element table using the LABEL subclause.
  • Multiple labels can be associated with the same graph element table.
  • The same label can be shared with multiple graph element tables.

    A label can be associated with more than one graph element table (shared label) provided the following conditions apply:

    • All graph element tables that share this label declare the same set of property names. Note that the property order does not matter in the label definition.
    • Different columns or value expression exposed by the same property name have union compatible types.
  • Also, refer to Type Compatibility Rules for Determining Property Types for more information.

Properties

  • By default, all the visible columns of a vertex or an edge table are automatically exposed as properties if there is no label declaration or if the DEFAULT LABEL subclause is used in the property graph definition. The property names are the same as the column names of the underlying database object.
  • Columns of any Oracle built-in data types can be exposed as properties of labels in a SQL property graph. This includes virtual columns, JSON data type columns, CLOB and BLOB data types.

    However, the following are not supported:

    • XMLType and SDO_GEOMETRY type columns are not supported.
    • SQL/XML value expressions over XMLType column stored as binary XML, and SDO_GEOMETRY built-in functions over SDO_GEOMETRY object datatype column are allowed as long as they return a value of a type supported for properties. Any general object data type and user defined data type and their corresponding SQL operator value expression over them are not supported.
    • Columns of type ANYTYPE cannot be exposed as property.
  • At the time of the SQL property graph creation, the data type of a vertex or edge property is determined as follows:
    • Distinct properties associated with distinct labels have the same data type as the underlying database columns.
    • Properties with the same name coming from different labels have the same data type as the underlying database columns. However, you must use the ALLOW MIXED PROPERTY TYPES option when creating the SQL property graph.

      See Using Graph Options to Create SQL Property Graphs for an example using a shared property name.

    • Properties with the same name coming from the same label will have the UNION ALL compatible type of the underlying database columns. In addition, you must use the ALLOW MIXED PROPERTY TYPES option when creating the SQL property graph:
  • If you want to explicitly define the vertex or edge properties for a label, then the following property declarations are supported:
    • PROPERTIES [ARE] ALL COLUMNS: To expose all the visible columns of the graph element table as label properties. However, if any columns are added or deleted in the source database object, after the creation of the SQL property graph, then these will not be reflected on the graph.
    • PROPERTIES [ARE] ALL COLUMNS EXCEPT(<column_names_list>): To expose all the visible columns of the graph element table as label properties except those that are explicitly listed.
    • PROPERTIES(<list_of_column_names>): To expose only those columns of the graph element table that are explicitly listed as label properties. The property name defaults to the column name.
    • PROPERTIES(<column_name AS property_name,...>): Same as the preceding option. However, if AS property_name is appended to the column_name, then property_name is used as the property name.
    • PROPERTIES(<column_expressions AS property_name,...>): To declare a property which is an expression over columns. The AS clause is mandatory in this case. A value expression can either be a SQL operator expression defined over scalar data type columns or JSON expression. See JSON Support in SQL Property Graphs for an example using JSON expressions.
    • NO PROPERTIES: No columns are exposed for a label.
  • Peudo-columns cannot be exposed as a label property.

4.1.4 Using Graph Options to Create SQL Property Graphs

You can use graph options to control the behavior of a SQL property graph at the time of its creation.

Graph options can be specified at the end of the CREATE PROPERTY GRAPH DDL statement using the OPTIONS clause. You can use either the MODE or MIXED PROPERTY TYPES option, or both as required.

Using an Option to Specify the Mode of the Graph

You can specify the MODE of the graph by using one of the following option values at the time of creating the SQL property graph:
  • ENFORCED MODE: This ensures that there is a dependency to the unique key constraint on the underlying database tables. If used when creating a SQL property graph, the CREATE PROPERTY GRAPH statement will throw an error if any of the following conditions apply:
    • The specified vertex or edge table KEY for the graph element table is neither a PRIMARY KEY nor a UNIQUE key defined on NOT NULL columns.
    • There is no explicit vertex or edge table KEY defined for the graph element table and also the system is unable to automatically identify the default vertex or edge key, as there is no single PRIMARY KEY or a single UNIQUE key constraint on NOT NULL columns on the underlying database table.
    • For a specified edge source key and corresponding source vertex key or for a specified edge destination key and corresponding destination vertex key, there does not exist a corresponding FOREIGN KEY between the underlying tables.
    • An edge table has no explicit keys for the source or for the destination and the system is unable to implicitly infer the keys, as there is no single FOREIGN KEY constraint between the edge table and the referenced source (or destination) vertex table.

    For example, consider the following t1 table in the database that does not have any primary key, unique key or a NOT NULL constraint.

    SQL> CREATE TABLE t1 (id NUMBER, name VARCHAR2(10));
    
    INSERT INTO t1 (id, name) VALUES (1,'John');
    INSERT INTO t1 (id, name) VALUES (2, 'Mary');

    Create a SQL property graph using OPTIONS(ENFORCED MODE) as shown:

    CREATE PROPERTY GRAPH g
       VERTEX TABLES (
          t1 KEY (id)
             LABEL t PROPERTIES ARE ALL COLUMNS
       ) OPTIONS(ENFORCED MODE);

    The graph creation fails with the following error as there are no key constraints to enforce uniqueness:

    ORA-42434: Columns used to define a graph element table key must be NOT NULL in ENFORCED MODE

    If you omit the KEY clause in the preceding graph definition, then the following error is thrown:

    ORA-42402: cannot infer key for graph element table T1

  • TRUSTED MODE (default): There is no dependency to the unique key constraint on the underlying database tables when using the TRUSTED mode. Therefore, the preceding example when run in TRUSTED mode will not throw any error. This implies that if you choose to use this option, then you must guarantee the uniqueness of primary keys on each of the graph element tables, as well as valid foreign key references between an edge table and its source and destination tables. Otherwise, your graph query results may be incorrect as the expected guarantees are not met.

Using an Option to Allow or Disallow Different Property Types for Shared Property Names

You can specify the MIXED PROPERTY TYPES options using one of the following values:
  • ALLOW MIXED PROPERTY TYPES: This ensures that:
    • If two properties with the same name belong to different labels, then they can have completely different types.

      For example, in addition to the sample tables persons and students (see Setting Up Sample Data in the Database), create the following additional table:

      CREATE TABLE t2 (id NUMBER, height VARCHAR2(4),
      CONSTRAINT t2_pk PRIMARY KEY (id));
      
      INSERT INTO t2 (id, height) VALUES (1, '1.80');
      INSERT INTO t2 (id, height) VALUES (2, '1.65');
      
      CREATE TABLE t3 (id NUMBER, height BINARY_DOUBLE,
      CONSTRAINT t3_pk PRIMARY KEY (id));
      
      INSERT INTO t3 (id, height) VALUES (1, 1.80);
      INSERT INTO t3 (id, height) VALUES (2, 1.65);

      Run the following CREATE PROPERTY GRAPH DDL statement which uses three distinct labels for the same property name, height.

      CREATE PROPERTY GRAPH g1
         VERTEX TABLES (
            persons
              LABEL person PROPERTIES (name, height),
            t2
              LABEL t2 PROPERTIES (height),
            t3
              LABEL t3 PROPERTIES (height)
         )OPTIONS(ALLOW MIXED PROPERTY TYPES);
      When the graph is created, the property type for height in the vertex tables associated with:
      • LABEL person is FLOAT
      • LABEL t2 is VARCHAR
      • LABEL t3 is BINARY_DOUBLE

      However, when querying this graph, the property type for height is dependent on the label constraint used in the GRAPH_TABLE query. See Accessing Label Properties for more information.

    • If you are sharing property names inside shared labels, then they should be all union compatible types.

      For example, run the following CREATE PROPERTY GRAPH DDL statement where the property name height is used inside the shared label t:

      CREATE PROPERTY GRAPH g2
         VERTEX TABLES (
            persons
              LABEL t PROPERTIES (height),
            t2
              LABEL t PROPERTIES (height)
         )OPTIONS(ALLOW MIXED PROPERTY TYPES);

    The graph creation fails as the column height in the tables persons and t2 has the data type FLOAT and VARCHAR respectively which are union incompatible. Therefore, the following error is thrown:

    ORA-42414: cannot use mixed type for property HEIGHT of label T

    However, the following graph will get created successfully as FLOAT and BINARY_DOUBLE belong to the numeric group and are union compatible.

    CREATE PROPERTY GRAPH g3
       VERTEX TABLES (
          persons
            LABEL t PROPERTIES (height),
          t3
            LABEL t PROPERTIES (height)
       )OPTIONS(ALLOW MIXED PROPERTY TYPES);

    See Type Compatibility Rules for Determining Property Types for more information.

  • DISALLOW MIXED PROPERTY TYPES (default): This ensures that a property with the same name should strictly be the same data type. This applies to all labels irrespective of whether they are associated with a single or multiple graph element tables.

    For example, run the following DDL statement using persons and t2 as the underlying database tables:

    CREATE PROPERTY GRAPH g4
       VERTEX TABLES (
          persons
            LABEL person PROPERTIES (name, height),
          t2
            LABEL t2 PROPERTIES (height)
       );

    The preceding code uses the default DISALLOW MIXED PROPERTY TYPES graph option and therefore throws an error as mixed property types are used in the graph definition:

    ORA-42414: cannot use mixed type for property HEIGHT of label T2

The following table summarizes compatibility rules with respect to the MIXED PROPERTY TYPES options

Description ALLOW DISALLOW
Properties with the same name exposed by shared labelsFoot 1 Union-compatible Types must match
Shared propertiesFoot 2 Any Types must match

Footnote 1 A label with the same name can be associated with more than one graph element table.

Footnote 2 A property with the same name can be exposed by different labels.

4.1.5 Granting System and Object Privileges for SQL Property Graphs

Oracle Database 23c introduces new system and object privileges for performing operations on SQL property graphs.

Table 4-1 System Privileges for SQL Property Graph Objects

System Privileges Description
CREATE PROPERTY GRAPH To create a SQL property graph in the grantee’s schema
CREATE ANY PROPERTY GRAPH To create a SQL property graph in any schema except SYS and AUDSYS
ALTER PROPERTY GRAPH To alter a SQL property graph in the grantee’s schema
ALTER ANY PROPERTY GRAPH To alter a SQL property graph in any schema except SYS and AUDSYS
READ PROPERTY GRAPH To query a SQL property graph in the grantee’s schema
READ ANY PROPERTY GRAPH To query a SQL property graph in any schema except SYS and AUDSY
SELECT PROPERTY GRAPH To query a SQL property graph in the grantee’s schema
DROP ANY PROPERTY GRAPH To drop a SQL property graph in any schema except SYS and AUDSYS

Table 4-2 Object Privileges for SQL Property Graphs

Object Privileges Description
ALTER To alter a SQL property graph
READ To query a SQL property graph with a GRAPH_TABLE query
Foot 3SELECT To query a SQL property graph with a GRAPH_TABLE query

Footnote 3

Note that the SELECT privilege behaves exactly as the READ privilege for the SQL property graph object. It is mainly present for compatibility with the SQL standards for a property graph object.

The following shows the examples for granting and revoking the SQL property graph related privileges. Ensure you have SYSDBA access to grant and revoke these privileges:

GRANT CREATE PROPERTY GRAPH, CREATE ANY PROPERTY GRAPH, 
      ALTER ANY PROPERTY GRAPH, DROP ANY PROPERTY GRAPH, 
      READ ANY PROPERTY GRAPH TO <graphuser>;
REVOKE CREATE PROPERTY GRAPH, CREATE ANY PROPERTY GRAPH, 
      ALTER ANY PROPERTY GRAPH, DROP ANY PROPERTY GRAPH, 
      READ ANY PROPERTY GRAPH FROM <graphuser>;

You can share your SQL property graph in the database with another user as shown.

GRANT SELECT ON PROPERTY GRAPH <graph_name> TO <schema_user>;

4.1.6 Retrieving SQL Creation DDL Using the DBMS_METADATA Package

You can retrieve the creation DDL for a SQL property graph using the DBMS_METADATA package.

The following example displays the DDL for the graph created in Creating a SQL Property Graph using the DBMS_METADATA package.

SQL> SELECT DBMS_METADATA.GET_DDL('PROPERTY_GRAPH', 'STUDENTS_GRAPH') FROM DUAL;

  CREATE PROPERTY GRAPH "GRAPHUSER"."STUDENTS_GRAPH"
  VERTEX TABLES (
   "GRAPHUSER"."PERSONS" AS "PERSONS" KEY ("PERSON_ID")
      LABEL PERSON PROPERTIES ("PERSON_ID", "NAME", "BIRTHDATE" AS "DOB")
      LABEL PERSON_HT PROPERTIES ("HEIGHT"),
   "GRAPHUSER"."UNIVERSITY" AS "UNIVERSITY" KEY ("ID")
      PROPERTIES ("ID", "NAME") )
  EDGE TABLES (
   "GRAPHUSER"."FRIENDS" AS "FRIENDS" KEY ("FRIENDSHIP_ID")
      SOURCE KEY("PERSON_A") REFERENCES PERSONS ("PERSON_ID")
      DESTINATION KEY("PERSON_B") REFERENCES PERSONS ("PERSON_ID")
     PROPERTIES ("FRIENDSHIP_ID", "MEETING_DATE"),
   "GRAPHUSER"."STUDENT_OF" AS "STUDENT_OF" KEY ("S_ID")
      SOURCE KEY("S_PERSON_ID") REFERENCES PERSONS ("PERSON_ID")
      DESTINATION KEY("S_UNIV_ID") REFERENCES PERSONS ("ID")
     PROPERTIES ("SUBJECT") )
  OPTIONS (TRUSTED MODE, DISALLOW MIXED PROPERTY TYPES)

4.1.7 Limitations of Creating a SQL Property Graph

This section lists a few restrictions that apply when creating a SQL property graph.

  • Hybrid partitioned tables, as well as views derived from these tables, cannot be used as graph element tables in a SQL property graph.
  • Database links, as well as views defined using these links, cannot be used as graph element tables in a SQL property graph.
  • Object tables (that is, table created with CREATE TABLE x OF myObjectType) and object views cannot be used as graph element tables in a SQL property graph.
  • XMLType table (that is, table created with CREATE TABLE x OF XMLTYPE ...) cannot be used as graph element tables in a SQL property graph. However SQL/XML operators, XMLExists(), XMLCast(XMLQuery()) over XMLType column stored as binary XML to define property as SQL value expression is supported.
  • Columns of type ANYTYPE cannot be exposed as properties or as keys for graph element tables.
  • Pseudo-columns cannot be exposed as properties or as keys for graph element tables.
  • Column expressions that comprise invocations to PL/SQL functions cannot be exposed as properties. Similarly, virtual columns defined over column expressions that comprise invocations to PL/SQL functions cannot be exposed as properties.
  • SQL property graph are not editionable.
  • A SQL property graph definition cannot be modified once the graph is created. However, you can redefine a SQL property graph using the OR REPLACE clause in the CREATE PROPERTY GRAPH DDL statement.
  • SQL property graph creation is not supported in a shard catalog. However, you can create a property graph over sharded tables in the local shards.

4.2 Revalidating a SQL Property Graph

Using the ALTER PROPERTY GRAPH COMPILE DDL statement, you can revalidate an existing property graph object in the database.

A SQL property graph schema may become invalid due to the alteration of the underlying database objects. For instance, adding or dropping a column from the underlying database tables, used in the graph definition, can cause the graph to become invalid. Any invalidation of the graph will also invalidate cursors depending on the graph object. In such a case, you can recover your property graph from an invalid state as shown in the following example. Also, refer to Granting System and Object Privileges for SQL Property Graphs to ensure you have the required privilege to perform the ALTER PROPERTY GRAPH operation.

Example 4-2 Revalidating a SQL Property Graph

ALTER PROPERTY GRAPH students_graph COMPILE;

See Also:

ALTER PROPERTY GRAPH in Oracle Database SQL Language Reference

4.3 Dropping a SQL Property Graph

Using the DROP PROPERTY GRAPH DDL statement, you can remove a property graph object in Oracle Database.

See Granting System and Object Privileges for SQL Property Graphs to ensure you have the required privilege to drop a SQL property graph.

Example 4-3 Dropping a SQL Property Graph

The following example removes the SQL property graph, students_graph, in the database.

DROP PROPERTY GRAPH students_graph;

Similar to database views, dropping a property graph object does not remove the underlying database tables.

See Also:

DROP PROPERTY GRAPH in Oracle Database SQL Language Reference

4.4 JSON Support in SQL Property Graphs

When creating a SQL property graph, you can define a label property over a JSON data type column using simplified dot notation. You can later access this property inside the GRAPH_TABLE query.

The label property defined over a JSON data type column can be of common SQL scalar data types, such as:
  • VARCHAR
  • NUMBER
  • BINARY_FLOAT
  • BINARY_DOUBLE
  • DATE
  • TIMESTAMP
  • raw JSON data converted to a SQL data type via .string(), .number(), .float(), .double(), .date(), .timestamp(), .binary() or their equivalent using the JSON_VALUE operator

Therefore, you can use either a JSON dot notation or the JSON_VALUE operator to select a scalar value in the JSON data to define a SQL property graph label property. This also applies when accessing a label property defined over the JSON data type column inside a GRAPH_TABLE query.

Example 4-4 Defining a SQL Property Graph Using JSON Dot Notation and JSON Expressions for Label Properties

The following example creates a SQL property graph that contains label properties defined over a JSON data type column. The graph is created using the sample database tables (persons and friendships) defined in Setting Up Sample Data in the Database. The example uses both the JSON dot notation and the JSON_VALUE expression to define the label property.

CREATE PROPERTY GRAPH friends_graph
  VERTEX TABLES (
      persons AS p KEY (person_id)
        LABEL person
          PROPERTIES (name, birthdate AS dob,
               p.hr_data.department.string() AS "works_in",
               JSON_VALUE(person_data, '$.role') AS "works_as")
  )
  EDGE TABLES (
      friends
        KEY (friendship_id)
        SOURCE KEY (person_a) REFERENCES p(person_id)
        DESTINATION KEY (person_b) REFERENCES p(person_id)
        PROPERTIES (meeting_date)
  );

The graph gets created successfully and you can query the graph as shown in the following example:

Example 4-5 Querying a SQL Property Graph and Accessing Label Properties Defined As SQL/JSON Expressions

The following example queries the SQL property graph created in the preceding example to access the label properties created over a JSON data type column.

SELECT * FROM GRAPH_TABLE (friends_graph
  MATCH
   (a IS person) -[e IS friends]-> (b IS person)
   COLUMNS (a.name AS a,
            a."works_in" AS "a_works_in",
            e.meeting_date,
            b.name AS b)
);

The query produces the following output:

A     a_works_in MEETING_D B
----- ---------- --------- -----
John  IT         01-SEP-00 Bob
Mary  HR         19-SEP-00 Alice
Mary  HR         19-SEP-00 John
Bob   IT         10-JUL-01 Mary

Example 4-6 Creating and Querying a SQL Property Graph with JSON Data Type Label Property

The following example creates a SQL property graph with JSON data type label property:

CREATE PROPERTY GRAPH friends_graph_new
  VERTEX TABLES (
      persons AS p KEY (person_id)
        LABEL person
          PROPERTIES (name, birthdate AS dob, p.hr_data AS "p_data")
  )
  EDGE TABLES (
      friends
        KEY (friendship_id)
        SOURCE KEY (person_a) REFERENCES p(person_id)
        DESTINATION KEY (person_b) REFERENCES p(person_id)
        PROPERTIES (meeting_date)
  );

You can then query the graph using a JSON_VALUE expression as shown:

SELECT * FROM GRAPH_TABLE (friends_graph_new
  MATCH
   (a IS person WHERE JSON_VALUE(a."p_data", '$.department') = 'IT') -[e]-> (b)
   COLUMNS (a.name AS a, 
           a."p_data".department.string() AS "a_works_in",
           a."p_data".role.string() AS "a_works_as",
           e.meeting_date,
           b.name AS b)
  );
A     a_works_in a_works_as           MEETING_D B
----- ---------- -------------------- --------- -----
John  IT         Software Developer   01-SEP-00 Bob
Bob   IT         Technical Consultant 10-JUL-01 Mary