Manage schemaless data

This page explains how to manage schemaless data in Spanner Graph. It also provides best practices and troubleshooting tips. We recommend that you are familiar with the Spanner Graph schema and queries.

Schemaless data management lets you create a flexible graph definition. You can add, update, or delete node and edge type definitions without schema changes. This approach supports iterative development and reduces schema management overhead, while preserving the familiar graph query experience.

Schemaless data management is useful for the following scenarios:

Managing graphs with frequent changes, such as updates and additions of element labels and properties.
Graphs with many node and edge types, which makes the creation and management of input tables cumbersome.

For more information about when to use schemaless data management, see Considerations for schemaless data management.

Model schemaless data

Spanner Graph lets you create a graph from tables that maps rows to nodes and edges. Instead of using separate tables for each element type, schemaless data modeling typically employs a single node table and a 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. Table names are for illustrative purposes—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 performs the following actions:

Stores all nodes in a single table, GraphNode, identified by a unique id.
Stores all edges in a single table, GraphEdge, identified by a unique 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 id to a dest_id pair.

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

For more information about key choices for schemaless data management, see Primary key definitions for nodes and edges.

Create a property graph

The CREATE PROPERTY GRAPH statement maps the input tables in the previous section as nodes and edges. Use the following clauses to define labels and properties for schemaless data:

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

The following example shows how to create a graph using these 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)
  );

Define dynamic labels

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

For more information about limitations when using dynamic labels, see Limitations.

Define dynamic properties

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

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

Considerations for 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 require frequent updates.
Your data is 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 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 such as STRING or INT64 is more effective than using dynamic properties with the 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 by 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

Spanner models top-level keys and values of the JSON data type 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;

The query returns results similar to the following:

JSON"Tom"

Convert property data types

Spanner treats properties as values of the JSON data type. In some cases, such as for comparisons with SQL types, you must first convert properties to a SQL type.

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

Converts the is_blocked property to a boolean type to evaluate the expression.
Converts the 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 must also convert the JSON data type. The following example converts the city property to a string type, which allows you to use it 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, perform rigorous type and value checks. Use strict converters when the JSON data type is known and enforced, for example, by 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. Use flexible converters when a rigorous check isn't required or types are difficult to enforce.

For more information about data conversion, see troubleshooting tips.

Filter by property values

In property filters, Spanner treats the filter parameters as values of JSON data type. For example, in the following query, Spanner treatsis_blocked 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, Spanner finds no match because 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

Spanner doesn't model nested JSON keys and values as properties. In the following example, Spanner doesn't model the JSON keys city, state, and country 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, this change directly causes mutations to the corresponding graph data. For more information about graph data mutation, see Insert, update, or delete Spanner Graph data.

Example queries

This section provides examples that show 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 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
}

Known limitations

This section lists the limitations of using schemaless data management.

Single table requirement for dynamic labels

You can only have one node table if a dynamic label is used in its definition. This restriction also applies to the edge table. Spanner doesn't permit the following:

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

You must store label string values as lowercase for matching. 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 you reference them 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

You must store property names 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;

Additional limitations

Spanner models only top-level keys of the JSON data type as properties.
Property data types must conform to the Spanner JSON type specifications.

Best practices for schemaless data

This section describes best practices that help you model schemaless data.

Define primary keys 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 multiple edges exist between two nodes, 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, 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.

Create a secondary index for a frequently accessed property

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

The following example shows how to add 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));

The following DDL statement 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 includes new columns that are available as graph node properties. To reflect this in the 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.

For more information, see updating existing node or edge definitions.

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, run the ANALYZE command after index creation so that the query optimizer is updated with the latest database statistics.

Use 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 label values

We recommend that you 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 lowercase label values and property names

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

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

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

The following example shows how to add 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 shows how to add 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 that properties exist

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 unique properties

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 type

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));

Combine defined and dynamic labels

Spanner allows nodes in the property graph to have both defined labels (defined in the schema) and dynamic labels (derived from data). 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.

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

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)));

Optimize batch updates of dynamic properties with DML

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

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

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

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

@node_ids: Keys of GraphNode rows, stored in an ARRAY parameter. If applicable, grouping and sorting them 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 multiple times in the `TO_JSON` result

Issue

The following node models the birthday and name properties as dynamic properties in its JSON column. Duplicate birthday and name properties 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 occurs because the JSON column (properties) is 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

To fix the following issues, 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 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 value conversion occurs 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 Stay organized with collections Save and categorize content based on your preferences.

Model schemaless data

Create input tables

Create a property graph

Define dynamic labels

Define dynamic properties

Considerations for 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

Example queries

Insert graph data

Update graph data

Delete graph data

Known limitations

Single table requirement for dynamic labels

Label names must be lowercase

Property names must be lowercase

Additional limitations

Best practices for schemaless data

Define primary keys for nodes and edges

Create a secondary index for a frequently accessed property

Use check constraints for data integrity

Enforce label values

Enforce lowercase label values and property names

Enforce that properties exist

Enforce unique properties

Enforce property data type

Combine defined and dynamic labels

Schema examples

Optimize batch updates of dynamic properties with DML

Troubleshoot

Property appears multiple times 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

Property appears multiple times in the `TO_JSON` result

`RETURN DISTINCT` property value use without conversion