4.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.
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 theJSON_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 4-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 4-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 4-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
Parent topic: SQL DDL Statements for Property Graphs