JSON extension
Usage
The json
extension adds support for JSON
objects, including a set of functions for JSON
access and manipulation, scanning from, and copying to JSON files. Using this extension, you can
interact with JSON files using LOAD FROM
,
COPY FROM
, and COPY TO
, similar to how you would
with CSV files.
The JSON functionality is not available by default, so you would first need to install the JSON extension by running the following commands:
Example dataset
Let’s look at an example dataset to demonstrate how the JSON extension can be used.
We have 3 JSON files that contain information about patients and their medical conditions. The
files are organized into two node files (person.json
and condition.json
) and one relationship
file (has_condition.json
).
In the following sections, we will first scan the JSON files to query its contents in Cypher, and then proceed to copy the JSON data and construct a graph.
Scan the JSON file
LOAD FROM
is a Cypher query that scans a file or object element by element, but doesn’t actually
move the data into a Kùzu table.
Because the JSON format contains simple data types without type information, the structure will be inferred.
To declare type information explicitly, you can use LOAD WITH HEADERS
like you would for CSV files.
To scan the file above, you can do the following:
Output:
Because info
is a nested object, its type in Kùzu is inferred as a STRUCT
, that itself contains
other types, like DOUBLE
, UINT8
, STRING
, and STRUCT
.
Missing keys
Missing keys, i.e., keys that are present in one JSON blob but not in another, are returned as the default/empty value for the type. To test this, let’s run another query to get the name, age, height, weight and insurance provider of all patients:
Output:
As can be seen, the patient Rebecca is new in the system and is missing her information fields:
age
is set to the default value of0
forUINT8
height
andweight
are set to the default value of0.0
forDOUBLE
insurance_provider
is set to an empty array[]
Enforcing types
To enforce the data type during scanning, use the LOAD WITH HEADERS
feature.
Example:
We can see that the types inside the info
STRUCT are now enforced to FLOAT
, rather than DOUBLE
.
Optional parameters
The following optional parameters are supported:
Name | Description |
---|---|
maximum_depth | Default value is 10 . Used by the type inference system to determine how “deep” into the json document to go to infer types. |
sample_size | Default value 2048 . Used by the type inference system to determine the number of elements used to infer the json type. |
Copy JSON files to a table
The COPY FROM
statement allows you to copy data from a JSON file into a node or relationship table in Kùzu.
In this section we will walk through the example dataset shown above and build a graph from the JSON data.
Copy to node tables
First, start by defining a node table schema that conforms to the JSON structure. For nested fields,
we declare a STRUCT
where necessary.
Example:
The syntax STRUCT( ... )[]
with the square braces at the end represents an arrya of STRUCTs.
You can then use a COPY FROM
statement to directly copy the contents of the JSON file into the
node table.
Similarly, we can define the node table for the patients’ medical conditions.
And copy the contents of condition.json
to the node table as follows:
Copy to relationship tables
To copy from a JSON file to a relationship table, the file must contain the "from"
and "to"
keys.
In the example dataset for has_condition.json
, we have these keys defined:
Any other keys that are not "from"
or "to"
are treated as relationship properties.
Let’s create a relationship table schema:
The has_condition.json
file can then directly be copied into the relationship table that was just created.
We obtain the following graph:
Any nested fields are ingested into the graph as STRUCTs. We can query on these nested fields as shown below:
Output:
Note how the UNWIND
clause was used to obtain individual records of the insurance providers for
each patient.
UNWIND
JSON arrays
In the above example, we have useful information about insurance providers that could also be used to capture the relationships between patients and their insurance providers.
Let’s model this using a new node table, InsuranceProvider
, and a new relationship table HAS_PROVIDER
.
We can then UNWIND
the insurance providers for each patient, obtain distinct providers, and then
pass these results via a subquery to COPY FROM
.
Let’s break down the above query step by step:
- The outer
COPY FROM
expects the result from the innerLOAD FROM
- The info
STRUCT
frompatient.json
is passed toUNWIND
so that we can obtain individual providers for each patient - A
DISTINCT
clause is used when returning the results of the subquery, because thename
of a provider is the primary key of theInsuranceProvider
node table per the schema created above (we cannot have duplicate values for primary keys).
We can do a similar sequence of steps to copy relationships from patient.json
as follows:
In this case, we didn’t alias the first two entries to from
and to
, like we did when copying
from the has_condition.json
file above. This is because the COPY FROM
query is looking for the
first two columns in the result as the FROM
and the TO
columns in the relationship, similar to
how it’s done in CSV.
We now obtain the following graph:
Copy query results to JSON files
Once you have the data in a graph, you can begin querying it in Cypher. You can use the COPY TO
statement to write the results of a query to a JSON file. Any query results of the type STRUCT
will be written as nested JSON. Two examples are shown below.
Say you want to write health insurance provider information and patient names for patients with the
condition “Migraine” to a JSON file named patient_providers.json
.
The output JSON would look like this:
Say we want to write the name of the condition and a list of patient names who have the condition
and have health insurance to a JSON file named patients_with_condition.json
. This is how we
would query the graph:
The output JSON would look like this:
Summary
When using the JSON extension, keep in mind the following considerations when copying data to Kùzu tables:
-
The order of the keys in the JSON file doesn’t need to match with the order of the columns defined in the schema (just the names need to match)
-
If directly copying from a JSON file to a relationship table, there need to be keys named
"from"
and"to"
in the file, whose values point to the primary key values of the underlying node tables. -
You can combine
LOAD FROM
subqueries withCOPY FROM
to have more control over the subset of JSON data being copied, as well as dynamically transform your data viaUNWIND
orDISTINCT
clauses, so it’s not necessary to write your relationships to an intermediate file prior to usingCOPY
.