Manage schemaless data with Spanner Graph

This page describes how to manage schemaless data within Spanner Graph. It also details best practices and troubleshooting tips. Familiarity with the Spanner Graph schema and queries is recommended.

Schemaless data management lets you create a flexible definition of a graph, where the node and edge type definitions can be added, updated, or deleted without schema changes. The approach supports iterative development and less schema management overhead, while also preserving the familiar graph query experience.

Schemaless data management is particularly useful for the following scenarios:

You manage graphs with frequent changes, such as updates and additions of element labels and properties.
Your graph has many node and edge types, making the creation and management of input tables challenging.

Model schemaless data

Spanner Graph lets you create a graph from tables where rows are mapped to nodes and edges. Instead of using separate tables for each element type, schemaless data modeling typically employs a single node table and single edge table with a STRING column for the label and a JSON column for properties.

Create input tables

You can create a single GraphNode table and a single GraphEdge table to store schemaless data, as shown in the following example. The table names are for illustrative purposes, and you can choose your own.

CREATE TABLE GraphNode (
  id INT64 NOT NULL,
  label STRING(MAX) NOT NULL,
  properties JSON,
) PRIMARY KEY (id);

CREATE TABLE GraphEdge (
  id INT64 NOT NULL,
  dest_id INT64 NOT NULL,
  edge_id INT64 NOT NULL,
  label STRING(MAX) NOT NULL,
  properties JSON,
) PRIMARY KEY (id, dest_id, edge_id),
  INTERLEAVE IN PARENT GraphNode;

This example does the following:

Stores all nodes in a single table GraphNode, uniquely identified by the id.
Stores all edges in a single table GraphEdge, uniquely identified by the combination of source (id), destination (dest_id), and its own identifier (edge_id). An edge_id is included as part of the primary key to permit more than one edge from an idto a dest_id pair.

Both the node and edge tables have their own label and properties columns of STRING and JSON type respectively.

Create a property graph

With the CREATE PROPERTY GRAPH statement, the input tables in the previous section are mapped as nodes and edges. You need to use the following clauses for defining labels and properties for schemaless data:

DYNAMIC LABEL: creates the label of a node or an edge from a STRING column from the input table.
DYNAMIC PROPERTIES: creates properties of a node or an edge from a JSON column from the input table.

The following example shows how to create a graph using those clauses:

CREATE PROPERTY GRAPH FinGraph
  NODE TABLES (
    GraphNode
      DYNAMIC LABEL (label)
      DYNAMIC PROPERTIES (properties)
  )
  EDGE TABLES (
    GraphEdge
      SOURCE KEY (id) REFERENCES GraphNode(id)
      DESTINATION KEY (dest_id) REFERENCES GraphNode(id)
      DYNAMIC LABEL (label)
      DYNAMIC PROPERTIES (properties)
  );

Dynamic label

The DYNAMIC LABEL clause designates a STRING data type column to store the label values.

For example, in a GraphNode row, if the label column has a person value, it maps to a Person node within the graph. Likewise, in a GraphEdge row, if the label column has a value of owns, it maps to an Owns edge within the graph.

Mapping a `GraphNode` label to a `GraphEdge` label

Dynamic properties

The DYNAMIC PROPERTIES clause designates a JSON data type column to store properties. JSON keys are the property names and JSON values are the property values.

For example, when a GraphNode row's properties column has JSON value '{"name": "David", "age": 43}', it maps to a node with the age and name properties, with 43 and "David" as property values.

When not to use schemaless data management

You might not want to use schemaless data management in the following scenarios:

The node and edge types for your graph data are well defined or their labels and properties don't need frequent updates.
Your data are already stored in Spanner and you prefer to build graphs from existing tables instead of introducing new, dedicated node and edge tables.
The limitations of schemaless data prevent your adoption.

In addition, if your workload is highly sensitive to write performance, especially when properties are frequently updated, using schema defined properties with primitive data types like STRING or INT64 is more effective than using dynamic properties with JSON type.

For more information about how to define the graph schema without using dynamic data labels and properties, see the Spanner Graph schema overview.

Query schemaless graph data

You can query schemaless graph data using Graph Query Language (GQL). You can use the sample queries in the Spanner Graph Query overview and GQL reference with limited modifications.

Match nodes and edges using labels

You can match nodes and edges using the label expression in GQL.

The following query matches connected nodes and edges that have the values account and transfers in their label column.

GRAPH FinGraph
MATCH (a:Account {id: 1})-[t:Transfers]->(d:Account)
RETURN COUNT(*) AS result_count;

Access properties

Top-level keys and values of the JSON data type are modeled as properties, such as age and name in the following example.

`JSON document`	`Properties`
`{ "name": "Tom", "age": 43, }`	`"name": "Tom"`
	`"age": 34`

The following example shows how to access the property name from the Person node.

GRAPH FinGraph
MATCH (person:Person {id: 1})
RETURN person.name;

This returns results similar to the following:

JSON"Tom"

Convert property data types

Properties are treated as values of the JSON data type. In some cases, such as for comparisons with SQL types, they need to be converted to a SQL type first.

In the following example, the query performs the following data type conversions:

Converts is_blocked property to a boolean type to evaluate the expression.
Converts order_number_str property to a string type and compares it with the literal value "302290001255747".
Uses LAX_INT64 function to safely convert order_number_str to an integer as the return type.

GRAPH FinGraph
MATCH (a:Account)-[t:Transfers]->()
WHERE BOOL(a.is_blocked) AND STRING(t.order_number_str) = "302290001255747"
RETURN LAX_INT64(t.order_number_str) AS order_number_as_int64;

This returns results similar to the following:

+-----------------------+
| order_number_as_int64 |
+-----------------------+
| 302290001255747       |
+-----------------------+

In clauses such as GROUP BY and ORDER BY, you also need to convert the JSON data type. The following example converts the city property to a string type, allowing it to be used for grouping.

GRAPH FinGraph
MATCH (person:Person {country: "South Korea"})
RETURN STRING(person.city) as person_city, COUNT(*) as cnt
LIMIT 10

Tips for converting JSON data types to SQL data types:

Strict converters, such as INT64, conduct rigorous type and value checks. This is recommended when the JSON data type is known and enforced, for example, using schema constraints to enforce the property data type.
Flexible converters, such as LAX_INT64, convert the value safely, when possible, and return NULL when conversion isn't feasible. This is recommended when a rigorous check isn't required or types are hard to enforce.

You can read more about data conversion in troubleshooting tips.

Filter by property values

In property filters, the filter parameters are treated as values of JSON data type. For example, in the following query, is_blocked is treated as a JSON boolean and order_number_str as a JSON string.

GRAPH FinGraph
MATCH (a:Account {is_blocked: false})-[t:Transfers {order_number_str:"302290001255747"}]->()
RETURN a.id AS account_id;

This returns results similar to the following:

+-----------------------+
| account_id            |
+-----------------------+
| 7                     |
+-----------------------+

The filter parameter must match the property type and value. For example, when the order_number_str filter parameter is an integer, no match is found since the property is a JSON string.

GRAPH FinGraph
MATCH (a:Account {is_blocked: false})-[t:Transfers {order_number_str: 302290001255747}]->()
RETURN t.order_number_str;

Access nested JSON properties

Nested JSON keys and values are not modeled as properties. In the following example, the JSON keys city, state, and country are not modeled as properties because they are nested under location. However, you can access them with a JSON field access operator or a JSON subscript operator.

`JSON document`	`Properties`
`{ "name": "Tom", "age": 43, "location": { "city": "New York", "state": "NY", "country": "USA", } }`	`"name": "Tom"`
	`"age": 34`
	`"location": { "city": "New York", "state": "NY", "country": "USA", }`

The following example shows how to access nested properties with the JSON field access operator.

GRAPH FinGraph
MATCH (person:Person {id: 1})
RETURN STRING(person.location.city);

This returns results similar to the following:

"New York"

Modify schemaless data

Spanner Graph maps data from tables to graph nodes and edges. When you change input table data, it directly causes mutations to the corresponding graph data. For more information about graph data mutation, see Insert, update, or delete Spanner Graph data.

Examples

This section provides examples for how to create, update, and delete graph data.

Insert graph data

The following example inserts a person node. Label and property names must use lowercase.

INSERT INTO GraphNode (id, label, properties)
VALUES (4, "person", JSON'{"name": "David", "age": 43}');

Update graph data

The following example updates an Account node and uses the JSON_SET function to set its is_blocked property.

UPDATE GraphNode
SET properties = JSON_SET(
  properties,
  '$.is_blocked', false
)
WHERE label = "account" AND id = 16;

The following example updates a person node with a new set of properties.

UPDATE GraphNode
SET properties = JSON'{"name": "David", "age": 43}'
WHERE label = "person" AND id = 4;

The following example uses the JSON_REMOVE function to remove the is_blocked property from an Account node. After execution, all other existing properties remain unchanged.

UPDATE GraphNode
SET properties = JSON_REMOVE(
  properties,
  '$.is_blocked'
)
WHERE label = "account" AND id = 16;

Delete graph data

The following example deletes the Transfers edge on Account nodes that have been transferred to blocked accounts.

DELETE FROM GraphEdge
WHERE label = "transfers" AND id IN {
  GRAPH FinGraph
  MATCH (a:Account)-[:Transfers]->{1,2}(:Account {is_blocked: TRUE})
  RETURN a.id
}

Limitations

This section lists the limitations of using schemaless data management.

Single table requirement for dynamic label

You can only have one node table if a dynamic label is used in its definition. This restriction also applies to the edge table. The following are disallowed:

Defining a node table with a dynamic label alongside any other node tables.
Defining an edge table with a dynamic label alongside any other edge tables.
Defining multiple node tables or multiple edge tables that each use a dynamic label.

For example, the following code fails when it tries to create multiple graph node with dynamic labels.

CREATE OR REPLACE PROPERTY GRAPH FinGraph
  NODE TABLES (
    GraphNodeOne
      DYNAMIC LABEL (label)
      DYNAMIC PROPERTIES (properties),
    GraphNodeTwo
      DYNAMIC LABEL (label)
      DYNAMIC PROPERTIES (properties),
    Account
      LABEL Account PROPERTIES(create_time)
  )
  EDGE TABLES (
    ...
  );

Label names must be lowercase

Label string values must be stored as lowercase to be matched. We recommend that you enforce this rule either in the application code or using schema constraints.

While label string values must be stored as lowercase, they aren't case sensitive when they're referenced in a query.

The following example shows how to insert labels in lowercase values:

INSERT INTO GraphNode (id, label) VALUES (1, "account");
INSERT INTO GraphNode (id, label) VALUES (2, "account");

You can use case-insensitive labels to match the GraphNode or GraphEdge.

GRAPH FinGraph
MATCH (accnt:Account {id: 1})-[:Transfers]->(dest_accnt:Account)
RETURN dest_accnt.id;

Property names must be lowercase

Property names must be stored in lowercase. We recommend that you enforce this rule either in the application code or using schema constraints.

While property names must be stored as lowercase, they aren't case sensitive when you reference them in your query.

The following example inserts the name and age properties using lowercase.

INSERT INTO GraphNode (id, label, properties)
VALUES (25, "person", JSON '{"name": "Kim", "age": 27}');

In query text, property names are case insensitive. For example, you can use either Age or age to access the property.

GRAPH FinGraph
MATCH (n:Person {Age: 27})
RETURN n.id;

Other limitations

Only top-level keys of the JSON data type are modeled as properties.
Property data types must conform to the Spanner JSON type specifications.

Best practices

This section describes best practices to model schemaless data.

Primary key definitions for nodes and edges

A node's key should be unique across all graph nodes. For example, as an INT64 or string UUID column.

If you have multiple edges between two nodes, you must introduce a unique identifier for the edge. The schema example uses an application logic INT64 edge_id column.

When you create the schema for node and edge tables, you can optionally include the label column as a primary key column, if the value is immutable. If you do this, the composite key formed by all key columns should be unique across all nodes or edges. This technique improves performance for queries that are only filtered by label.

For more information about primary key choice, see Choose a primary key.

Secondary index for a frequently accessed property

To boost query performance for a property frequently used in filters, you can create a secondary index against a generated property column, and then use it in graph schema and queries.

The following example adds a generated age column to the GraphNode table for a person node. The value is NULL for nodes without the person label.

ALTER TABLE GraphNode
ADD COLUMN person_age INT64 AS
(IF (label = "person", LAX_INT64(properties.age), NULL));

It then creates a NULL FILTERED INDEX for person_age and interleaves it into the GraphNode table for local access.

CREATE NULL_FILTERED INDEX IdxPersonAge
ON GraphNode(id, label, person_age), INTERLEAVE IN GraphNode;

The GraphNode table now includes new columns that are available as graph node properties. To reflect this in your property graph definition, use the CREATE OR REPLACE PROPERTY GRAPH statement. This recompiles the definition and includes the new person_age column as a property.

The following statement recompiles the definition and includes the new person_age column as a property.

CREATE OR REPLACE PROPERTY GRAPH FinGraph
  NODE TABLES (
    GraphNode
      DYNAMIC LABEL (label)
      DYNAMIC PROPERTIES (properties)
  )
  EDGE TABLES (
    GraphEdge
      SOURCE KEY (id) REFERENCES GraphNode (id)
      DESTINATION KEY (dest_id) REFERENCES GraphNode (id)
      DYNAMIC LABEL (label)
      DYNAMIC PROPERTIES (properties)
  );

The following example runs a query with the indexed property.

GRAPH FinGraph
MATCH (person:Person {person_age: 43})
RETURN person.id, person.name;

Optionally, you can run the ANALYZE command after index creation so that the query optimizer is updated with the latest database statistics.

Check constraints for data integrity

Spanner supports schema objects such as check constraints to enforce label and property data integrity. This section lists recommendations for check constraints that you can use with schemaless data.

Enforce valid label values

We recommended that you to use NOT NULL in the label column definition to avoid undefined label values.

CREATE TABLE GraphNode (
  id INT64 NOT NULL,
  label STRING(MAX) NOT NULL,
  properties JSON,
) PRIMARY KEY (id);

Enforce the lowercase label values and property names

Because label and property names must be stored as lowercase values, we recommend that you do either of the following:

Enforce the check in your application logic.
Create check constraints in the schema.

At query time, the label and property name are case insensitive.

The following example adds a node label constraint to the GraphNode table to ensure the label is in lowercase.

ALTER TABLE GraphNode ADD CONSTRAINT NodeLabelLowerCaseCheck
CHECK(LOWER(label) = label);

The following example adds a check constraint to the edge property name. The check uses JSON_KEYS to access the top-level keys. COALESCE converts the output to an empty array if JSON_KEYS returns NULL and then checks that each key is lowercase.

ALTER TABLE GraphEdge ADD CONSTRAINT EdgePropertiesLowerCaseCheck
CHECK(NOT array_includes(COALESCE(JSON_KEYS(properties, 1), []), key->key<>LOWER(key)));

Enforce property existence

You can create a constraint that checks if a property exists for a label.

In the following example, the constraint checks if a person node has a name property.

ALTER TABLE GraphNode
ADD CONSTRAINT NameMustExistForPersonConstraint
CHECK (IF(label = 'person', properties.name IS NOT NULL, TRUE));

Enforce property uniqueness

You can create property-based constraints that check if the property of a node or edge is unique across nodes or edges with the same label. To do this, use a UNIQUE INDEX against the generated columns of properties.

In the following example, the unique index checks that the name and country properties combined are unique for any person node.

Add a generated column for PersonName.

ALTER TABLE GraphNode
ADD COLUMN person_name STRING(MAX)
AS (IF(label = 'person', STRING(properties.name), NULL)) Hidden;

Add a generated column for PersonCountry.

ALTER TABLE GraphNode
ADD COLUMN person_country STRING(MAX)
AS (IF(label = 'person', STRING(properties.country), NULL)) Hidden;

Create a NULL_FILTERED unique index against the PersonName and PersonCountry properties.

CREATE UNIQUE NULL_FILTERED INDEX NameAndCountryMustBeUniqueForPerson
ON GraphNode (person_name, person_country);

Enforce property data types

You can enforce a property data type using a data type constraint on a property value for a label, as shown in the following example. This example uses the JSON_TYPE function to check that the name property of the person label uses the STRING type.

ALTER TABLE GraphNode
ADD CONSTRAINT PersonNameMustBeStringTypeConstraint
CHECK (IF(label = 'person', JSON_TYPE(properties.name) = 'string', TRUE));

Combining defined and dynamic labels

Spanner lets nodes in your property graph have both defined labels (defined in the schema) and dynamic labels (derived from data). You can customize labels to use this flexibility.

Consider the following schema that shows the creation of the GraphNode table:

CREATE OR REPLACE PROPERTY GRAPH FinGraph
  NODE TABLES (
    GraphNode
      LABEL Entity -- Defined label
      DYNAMIC LABEL (label) -- Dynamic label from data column 'label'
      DYNAMIC PROPERTIES (properties)
  );

Here, every node created from GraphNode has the defined label Entity. In addition, each node has a dynamic label determined by the value in its label column.

You can then write queries that match nodes based on either label type. For example, the following query finds nodes using the defined Entity label:

GRAPH FinGraph
MATCH (node:Entity {id: 1}) -- Querying by the defined label
RETURN node.name;

Even though this query uses the defined label Entity, remember that the matched node also carries a dynamic label based on its data.

Schema examples

You can use the schema examples in this section as templates to create your own schemas. Key schema components include the following:

Graph input tables creation
Property graph creation
Optional: reverse edge traversal index to boost reverse traversal performance
Optional: label index to boost performance of queries by labels
Optional: schema constraints to enforce lowercase labels and property names

The following example shows how to create input tables and a property graph:

CREATE TABLE GraphNode (
  id INT64 NOT NULL,
  label STRING(MAX) NOT NULL,
  properties JSON
) PRIMARY KEY (id);

CREATE TABLE GraphEdge (
  id INT64 NOT NULL,
  dest_id INT64 NOT NULL,
  edge_id INT64 NOT NULL,
  label STRING(MAX) NOT NULL,
  properties JSON
) PRIMARY KEY (id, dest_id, edge_id),
  INTERLEAVE IN PARENT GraphNode;

CREATE PROPERTY GRAPH FinGraph
  NODE TABLES (
    GraphNode
      DYNAMIC LABEL (label)
      DYNAMIC PROPERTIES (properties)
  )
  EDGE TABLES (
    GraphEdge
      SOURCE KEY (id) REFERENCES GraphNode(id)
      DESTINATION KEY (dest_id) REFERENCES GraphNode(id)
      DYNAMIC LABEL (label)
      DYNAMIC PROPERTIES (properties)
  );

The following example uses an index to improve reverse edge traversal. The STORING (properties) clause includes a copy of edge properties, which speeds up queries that filter on these properties. You can omit the STORING (properties) clause if your queries don't benefit from it.

CREATE INDEX R_EDGE ON GraphEdge (dest_id, id, edge_id) STORING (properties),
INTERLEAVE IN GraphNode;

The following example uses a label index to speed up matching nodes by labels.

CREATE INDEX IDX_NODE_LABEL ON GraphNode (label);

The following example adds constraints that enforce lowercase labels and properties. The last two examples use the JSON_KEYS function. Optionally, you can enforce the lowercase check in application logic.

ALTER TABLE GraphNode ADD CONSTRAINT node_label_lower_case
CHECK(LOWER(label) = label);

ALTER TABLE GraphEdge ADD CONSTRAINT edge_label_lower_case
CHECK(LOWER(label) = label);

ALTER TABLE GraphNode ADD CONSTRAINT node_property_keys_lower_case
CHECK(
  NOT array_includes(COALESCE(JSON_KEYS(properties, 1), []), key->key<>LOWER(key)));

ALTER TABLE GraphEdge ADD CONSTRAINT edge_property_keys_lower_case
CHECK(
  NOT array_includes(COALESCE(JSON_KEYS(properties, 1), []), key->key<>LOWER(key)));

Optimizing batch updates of dynamic properties with DML

Modifying dynamic properties using functions like JSON_SET and JSON_REMOVE involves read-modify-write operations. They can lead to higher cost compared to updating properties of STRING or INT64 type.

If your workloads involve batch updates to dynamic properties using DML, consider the following recommendations to achieve better performance:

Update multiple rows in a single DML statement instead of processing rows individually.
When updating a wide key range, group and sort the affected rows by their primary keys. Updating non-overlapping ranges with each DML reduces lock contention.
Use query parameters in DML statements rather than hard coding them to improve performance.

Based on these suggestions, the following example updates the is_blocked property for 100 nodes in a single DML statement. The query parameters include the following:

@node_ids: the keys of GraphNode rows, stored in an ARRAY parameter. If applicable, having them grouped and sorted across DMLs achieves better performance.
@is_blocked_values: the corresponding values to be updated, stored in an ARRAY parameter.

UPDATE GraphNode
SET properties = JSON_SET(
  properties,
  '$.is_blocked',
  CASE id
    WHEN @node_ids[OFFSET(0)] THEN @is_blocked_values[OFFSET(0)]
    WHEN @node_ids[OFFSET(1)] THEN @is_blocked_values[OFFSET(1)]
    ...
    WHEN @node_ids[OFFSET(99)] THEN @is_blocked_values[OFFSET(99)]
  END,
  create_if_missing => TRUE)
WHERE id IN UNNEST(@node_ids)

Troubleshoot

This section describes how to troubleshoot issues with schemaless data.

Property appears more than once in the `TO_JSON` result

Issue

The following node models the birthday and name properties as dynamic properties in its JSON column. Duplicate properties of birthday and name appear in the graph element JSON result.

GRAPH FinGraph
MATCH (n: Person {id: 14})
RETURN SAFE_TO_JSON(n) AS n;

This returns results similar to the following:

{
  …,
  "properties": {
    "birthday": "1991-12-21 00:00:00",
    "name": "Alex",
    "id": 14,
    "label": "person",
    "properties": {
      "birthday": "1991-12-21 00:00:00",
      "name": "Alex"
    }
  }
  …
}

Possible cause

By default, all columns of the base table are defined as properties. Using TO_JSON or SAFE_TO_JSON to return graph elements results in duplicate properties. This is due to the JSON column (that is, properties) being a schema-defined property, while the first-level keys of the JSON are modeled as dynamic properties.

Recommended solution

To avoid this behavior, use the PROPERTIES ALL COLUMNS EXCEPT clause to exclude the properties column when you define properties in the schema, as shown in the following example:

CREATE OR REPLACE PROPERTY GRAPH FinGraph
  NODE TABLES (
    GraphNode
      PROPERTIES ALL COLUMNS EXCEPT (properties)
      DYNAMIC LABEL (label)
      DYNAMIC PROPERTIES (properties)
  );

After the schema change, the returned graph elements of the JSON data type don't have duplicates.

GRAPH FinGraph
MATCH (n: Person {id: 1})
RETURN TO_JSON(n) AS n;

This query returns the following:

{
  …
  "properties": {
    "birthday": "1991-12-21 00:00:00",
    "name": "Alex",
    "id": 1,
    "label": "person",
  }
}

Common issues when property values aren't properly converted

A common fix to the following issues is to always use property value conversions when using a property inside a query expression.

Property values comparison without conversion

Issue

No matching signature for operator = for argument types: JSON, STRING

Possible cause

The query doesn't properly convert property values. For example, the name property is not converted to STRING type in comparison:

GRAPH FinGraph
MATCH (p:Person)
WHERE p.name = "Alex"
RETURN p.id;

Recommended solution

To fix this issue, use a value conversion before the comparison.

GRAPH FinGraph
MATCH (p:Person)
WHERE STRING(p.name) = "Alex"
RETURN p.id;

This returns results similar to the following:

+------+
| id   |
+------+
| 1    |
+------+

Alternatively, use a property filter to simplify equality comparisons where the value conversion is done automatically. Notice that the value's type ("Alex") must exactly match the property's STRING type in JSON.

GRAPH FinGraph
MATCH (p:Person {name: 'Alex'})
RETURN p.id;

This returns results similar to the following:

+------+
| id   |
+------+
| 1    |
+------+

`RETURN DISTINCT` property value use without conversion

Issue

Column order_number_str of type JSON cannot be used in `RETURN DISTINCT

Possible cause

In the following example, order_number_str hasn't been converted before it's used in the RETURN DISTINCT statement:

GRAPH FinGraph
MATCH -[t:Transfers]->
RETURN DISTINCT t.order_number_str AS order_number_str;

Recommended solution

To fix this issue, use a value conversion before RETURN DISTINCT.

GRAPH FinGraph
MATCH -[t:Transfers]->
RETURN DISTINCT STRING(t.order_number_str) AS order_number_str;

This returns results similar to the following:

+-----------------+
| order_number_str|
+-----------------+
| 302290001255747 |
| 103650009791820 |
| 304330008004315 |
| 304120005529714 |
+-----------------+

Property used as a grouping key without conversion

Issue

Grouping by expressions of type JSON is not allowed.

Possible cause

In the following example, t.order_number_str isn't converted before it's used to group JSON objects:

GRAPH FinGraph
MATCH (a:Account)-[t:Transfers]->(b:Account)
RETURN t.order_number_str, COUNT(*) AS total_transfers;

Recommended solution

To fix this issue, use a value conversion before using the property as a grouping key.

GRAPH FinGraph
MATCH (a:Account)-[t:Transfers]->(b:Account)
RETURN STRING(t.order_number_str) AS order_number_str, COUNT(*) AS total_transfers;

This returns results similar to the following:

+-----------------+------------------+
| order_number_str | total_transfers |
+-----------------+------------------+
| 302290001255747 |                1 |
| 103650009791820 |                1 |
| 304330008004315 |                1 |
| 304120005529714 |                2 |
+-----------------+------------------+

Property used as an ordering key without conversion

Issue

ORDER BY does not support expressions of type JSON

Possible cause

In the following example, t.amount isn't converted before it's used for ordering results:

GRAPH FinGraph
MATCH (a:Account)-[t:Transfers]->(b:Account)
RETURN a.Id AS from_account, b.Id AS to_account, t.amount
ORDER BY t.amount DESC
LIMIT 1;

Recommended solution

To fix this issue, do a conversion on t.amount in the ORDER BY clause.

GRAPH FinGraph
MATCH (a:Account)-[t:Transfers]->(b:Account)
RETURN a.Id AS from_account, b.Id AS to_account, t.amount
ORDER BY DOUBLE(t.amount) DESC
LIMIT 1;

This returns results similar to the following:

+--------------+------------+--------+
| from_account | to_account | amount |
+--------------+------------+--------+
|           20 |          7 | 500    |
+--------------+------------+--------+

Type mismatch during conversion

Issue

The provided JSON input is not an integer

Possible cause

In the following example, the order_number_str property is stored as a JSON STRING data type. If you try to perform a conversion to INT64, it returns an error.

GRAPH FinGraph
MATCH -[e:Transfers]->
WHERE INT64(e.order_number_str) = 302290001255747
RETURN e.amount;

Recommended solution

To fix this issue, use the exact value converter that matches the value type.

GRAPH FinGraph
MATCH -[e:Transfers]->
WHERE STRING(e.order_number_str) = "302290001255747"
RETURN e.amount;

This returns results similar to the following:

+-----------+
| amount    |
+-----------+
| JSON"200" |
+-----------+

Alternatively, use a flexible converter when the value is convertible to the target type, as shown in the following example:

GRAPH FinGraph
MATCH -[e:Transfers]->
WHERE LAX_INT64(e.order_number_str) = 302290001255747
RETURN e.amount;

This returns results similar to the following:

+-----------+
| amount    |
+-----------+
| JSON"200" |
+-----------+

What's next

To learn more about JSON, see Modify JSON data and JSON functions list.
Compare Spanner Graph and openCypher.
Migrate to Spanner Graph.

Manage schemaless data with Spanner Graph Stay organized with collections Save and categorize content based on your preferences.

Model schemaless data

Create input tables

Create a property graph

Dynamic label

Dynamic properties

When not to use schemaless data management

Query schemaless graph data

Match nodes and edges using labels

Access properties

Convert property data types

Filter by property values

Access nested JSON properties

Modify schemaless data

Examples

Insert graph data

Update graph data

Delete graph data

Limitations

Single table requirement for dynamic label

Label names must be lowercase

Property names must be lowercase

Other limitations

Best practices

Primary key definitions for nodes and edges

Secondary index for a frequently accessed property

Check constraints for data integrity

Enforce valid label values

Enforce the lowercase label values and property names

Enforce property existence

Enforce property uniqueness

Enforce property data types

Combining defined and dynamic labels

Schema examples

Optimizing batch updates of dynamic properties with DML

Troubleshoot

Property appears more than once in the TO_JSON result

Common issues when property values aren't properly converted

Property values comparison without conversion

RETURN DISTINCT property value use without conversion

Property used as a grouping key without conversion

Property used as an ordering key without conversion

Type mismatch during conversion

What's next

Manage schemaless data with Spanner Graph

Property appears more than once in the `TO_JSON` result

`RETURN DISTINCT` property value use without conversion