5.5 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 SQL graph 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 SQL graph query.

Example 5-5 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 5-6 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 5-7 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