Note: This feature is available with the Spanner Enterprise edition and Enterprise Plus edition. For more information, see theSpanner editions overview.

This page explains how to manage schemaless data in Spanner Graph. It alsoprovidesbest practices andtroubleshootingtips. We recommend that you are familiar with theSpanner Graphschema andqueries.

Schemaless data management lets you create a flexible graph definition.You can add, update, or delete node and edge type definitions without schemachanges. This approach supports iterative development and reduces schemamanagement 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 ofelement labels and properties.

• Graphs with many node and edge types, which makes the creation andmanagement of input tables cumbersome.

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

Model schemaless data

Spanner Graph lets you create a graph from tables that maps rows tonodes and edges.Instead of using separate tables for each element type, schemaless data modelingtypically employs a single node table and a single edge table with aSTRINGcolumn for the label and aJSON column for properties.

Create input tables

You can create a singleGraphNode table and a singleGraphEdge table tostore schemaless data, as shown in the following example. Table names are forillustrative purposes—you can choose your own.

CREATETABLEGraphNode(idINT64NOTNULL,labelSTRING(MAX)NOTNULL,propertiesJSON,)PRIMARYKEY(id);CREATETABLEGraphEdge(idINT64NOTNULL,dest_idINT64NOTNULL,edge_idINT64NOTNULL,labelSTRING(MAX)NOTNULL,propertiesJSON,)PRIMARYKEY(id,dest_id,edge_id),INTERLEAVEINPARENTGraphNode;

This example performs the following actions:

• Stores all nodes in a single table,GraphNode, identified by a uniqueid.

• Stores all edges in a single table,GraphEdge, identified by a uniquecombination of source (id), destination (dest_id), and its ownidentifier (edge_id). Anedge_id is included as part of the primary keyto permit more than one edge from anid to adest_id pair.

Both the node and edge tables have their ownlabel andproperties columns.These columns are of typeSTRING andJSON, respectively.

For more information about key choices for schemaless data management, seePrimary key definitions for nodes andedges.

Create a property graph

TheCREATE PROPERTYGRAPHstatement maps the input tables in the previous section as nodes and edges. Usethe following clauses to define labels and properties for schemaless data:

• DYNAMIC LABEL: creates the label of a node or an edge from aSTRINGcolumn that's from the input table.
• DYNAMIC PROPERTIES: creates properties of a node or an edge from aJSONcolumn that's from the input table.

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

CREATEPROPERTYGRAPHFinGraphNODETABLES(GraphNodeDYNAMICLABEL(label)DYNAMICPROPERTIES(properties))EDGETABLES(GraphEdgeSOURCEKEY(id)REFERENCESGraphNode(id)DESTINATIONKEY(dest_id)REFERENCESGraphNode(id)DYNAMICLABEL(label)DYNAMICPROPERTIES(properties));

Define dynamic labels

TheDYNAMIC LABEL clause designates aSTRING data type column to store thelabel values.

For example, in aGraphNode row, if thelabel column has aperson value,it maps to aPerson node within the graph. Likewise, in aGraphEdge row, ifthe label column has a value ofowns, it maps to anOwns edge within thegraph.

For more information about limitations when using dynamic labels, seeLimitations.

Define dynamic properties

TheDYNAMIC PROPERTIES clause designates aJSON data type column to storeproperties. JSON keys represent property names, and JSON values representproperty values.

For example, when aGraphNode row'sproperties column has the JSON value'{"name": "David", "age": 43}', Spanner maps it to a node thathasage andname properties with43 and"David" as their respectivevalues.

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 theirlabels and properties don't require frequent updates.
• Your data is already stored in Spanner, and you prefer tobuild graphs from existing tables instead of introducing new, dedicated nodeand edge tables.
• Thelimitations of schemaless data preventadoption.

In addition, if your workload is highly sensitive to write performance,especially when properties are frequently updated, usingschema definedproperties withprimitive data types such asSTRING orINT64 is more effective than usingdynamic properties with theJSON type.

For more information about how to define the graph schema without using dynamicdata labels and properties, see theSpanner Graph schemaoverview.

Query schemaless graph data

You can query schemaless graph data usingGraph Query Language (GQL).You can use the sample queries in theSpanner Graph Query overviewandGQL referencewith 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 valuesaccount andtransfers in their label column.

GRAPHFinGraphMATCH(a:Account{id:1})-[t:Transfers]->(d:Account)RETURNCOUNT(*)ASresult_count;

Access properties

Spanner models top-level keys and values of theJSON data typeas properties, such asage andname in the following example.

   {     "name": "Tom",     "age": 43,   }

"name": "Tom"

"age": 43

The following example shows how to access the propertyname from thePersonnode.

GRAPHFinGraphMATCH(person:Person{id:1})RETURNperson.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. Insome cases, such as for comparisons with SQL types, you must first convertproperties to a SQL type.

In the following example, the query performs the following data typeconversions:

• Converts theis_blocked property to a boolean type to evaluate theexpression.
• Converts theorder_number_str property to a string type and compares itwith the literal value"302290001255747".
• UsesLAX_INT64function to safely convertorder_number_str to an integer as the returntype.

GRAPHFinGraphMATCH(a:Account)-[t:Transfers]->()WHEREBOOL(a.is_blocked)ANDSTRING(t.order_number_str)="302290001255747"RETURNLAX_INT64(t.order_number_str)ASorder_number_as_int64;

This returns results similar to the following:

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

In clauses such asGROUP BY andORDER BY, you must also convert the JSONdata type. The following example converts thecity property to a string type,which allows you to use it for grouping.

GRAPHFinGraphMATCH(person:Person{country:"South Korea"})RETURNSTRING(person.city)asperson_city,COUNT(*)ascntLIMIT10

Tips for converting JSON data types to SQL data types:

• Strict converters, such asINT64,perform rigorous type and value checks. Use strict converters when the JSONdata type is known and enforced, for example, by using schema constraints toenforce the property data type.
• Flexible converters, such asLAX_INT64,convert the value safely when possible, and returnNULL when conversionisn't feasible. Use flexible converters when a rigorous check isn't requiredor types are difficult to enforce.

For more information about data conversion, seetroubleshootingtips.

Filter by property values

Inproperty filters,Spanner treats the filter parameters as values ofJSON datatype. For example, in the following query, Spannertreatsis_blocked as a JSONboolean andorder_number_str as a JSONstring.

GRAPHFinGraphMATCH(a:Account{is_blocked:false})-[t:Transfers{order_number_str:"302290001255747"}]->()RETURNa.idASaccount_id;

This returns results similar to the following:

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

The filter parameter must match the property type and value. For example, whentheorder_number_str filter parameter is an integer, Spannerfinds no match because the property is a JSONstring.

GRAPHFinGraphMATCH(a:Account{is_blocked:false})-[t:Transfers{order_number_str:302290001255747}]->()RETURNt.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 keyscity,state, andcountry as properties because they are nested underlocation. However, you can access them with a JSONfield access operatoror a JSONsubscript operator.

   {     "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 fieldaccess operator.

GRAPHFinGraphMATCH(person:Person{id:1})RETURNSTRING(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 youchange input table data, this change directly causes mutations to thecorresponding graph data. For more information about graph data mutation, seeInsert, update, or delete Spanner Graph data.

Example queries

This section provides examples that show how to create, update, and delete graphdata.

Insert graph data

The following example inserts aperson node. Label and property names mustuse lowercase.

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

Update graph data

The following example updates anAccount node and uses theJSON_SETfunction to set itsis_blocked property.

UPDATEGraphNodeSETproperties=JSON_SET(properties,'$.is_blocked',false)WHERElabel="account"ANDid=16;

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

UPDATEGraphNodeSETproperties=JSON'{"name": "David", "age": 43}'WHERElabel="person"ANDid=4;

The following example uses theJSON_REMOVEfunction to remove theis_blocked property from anAccount node. Afterexecution, all other existing properties remain unchanged.

UPDATEGraphNodeSETproperties=JSON_REMOVE(properties,'$.is_blocked')WHERElabel="account"ANDid=16;

Delete graph data

The following example deletes theTransfers edge onAccount nodes thattransferred to blocked accounts.

DELETEFROMGraphEdgeWHERElabel="transfers"ANDidIN{GRAPHFinGraphMATCH(a:Account)-[:Transfers]->{1,2}(:Account{is_blocked:TRUE})RETURNa.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. Spannerdoesn'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 adynamic label.

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

CREATEORREPLACEPROPERTYGRAPHFinGraphNODETABLES(GraphNodeOneDYNAMICLABEL(label)DYNAMICPROPERTIES(properties),GraphNodeTwoDYNAMICLABEL(label)DYNAMICPROPERTIES(properties),AccountLABELAccountPROPERTIES(create_time))EDGETABLES(...);

Label names must be lowercase

You must store label string values as lowercase for matching. We recommend thatyou enforce this rule either in the application code or using schemaconstraints.

While label string values must be stored as lowercase, they aren't casesensitive when you reference them in a query.

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

INSERTINTOGraphNode(id,label)VALUES(1,"account");INSERTINTOGraphNode(id,label)VALUES(2,"account");

You can use case-insensitive labels to match theGraphNode orGraphEdge.

GRAPHFinGraphMATCH(accnt:Account{id:1})-[:Transfers]->(dest_accnt:Account)RETURNdest_accnt.id;

Property names must be lowercase

You must store property names in lowercase. We recommend that you enforce thisrule either in the application code or using schemaconstraints.

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

The following example inserts thename andage properties using lowercase.

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

In query text, property names are case insensitive. For example, you can useeitherAge orage to access the property.

GRAPHFinGraphMATCH(n:Person{Age:27})RETURNn.id;

Additional limitations

• Spanner models onlytop-level keys oftheJSON data type as properties.
• Property data types must conform to the Spanner JSON typespecifications.

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 anINT64or stringUUIDcolumn.

If multiple edges exist between two nodes, introduce a unique identifier for theedge. Theschema example uses an application logicINT64edge_id column.

When you create the schema for node and edge tables, optionally include thelabel column as aprimary keycolumn if the valueis immutable. If you do this, the composite key formed by all key columns shouldbe unique across all nodes or edges. This technique improves performance forqueries that are only filtered by label.

For more information about primary key choice, seeChoose a primarykey.

Create a secondary index for a frequently accessed property

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

The following example shows how to add a generatedage column to theGraphNode table for aperson node. The value isNULL for nodes without theperson label.

ALTERTABLEGraphNodeADDCOLUMNperson_ageINT64AS(IF(label="person",LAX_INT64(properties.age),NULL));

The following DDL statement then creates aNULL FILTERED INDEX forperson_age and interleaves it into theGraphNode table for local access.

CREATENULL_FILTEREDINDEXIdxPersonAgeONGraphNode(id,label,person_age),INTERLEAVEINGraphNode;

TheGraphNode table includes new columns that are available as graph nodeproperties. To reflect this in the property graph definition, use theCREATE ORREPLACE PROPERTY GRAPH statement. This recompiles the definition and includesthe newperson_age column as a property.

For more information, seeupdating existing node or edge definitions.

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

CREATEORREPLACEPROPERTYGRAPHFinGraphNODETABLES(GraphNodeDYNAMICLABEL(label)DYNAMICPROPERTIES(properties))EDGETABLES(GraphEdgeSOURCEKEY(id)REFERENCESGraphNode(id)DESTINATIONKEY(dest_id)REFERENCESGraphNode(id)DYNAMICLABEL(label)DYNAMICPROPERTIES(properties));

The following example runs a query with the indexed property.

GRAPHFinGraphMATCH(person:Person{person_age:43})RETURNperson.id,person.name;

Optionally, run theANALYZEcommandafter index creation so that the query optimizer is updated with the latestdatabase statistics.

Use check constraints for data integrity

Spanner supports schema objects such ascheckconstraints to enforce label andproperty data integrity. This section lists recommendations for checkconstraints that you can use with schemaless data.

Enforce label values

We recommend that you useNOT NULL in the label column definition to avoidundefined label values.

CREATETABLEGraphNode(idINT64NOTNULL,labelSTRING(MAX)NOTNULL,propertiesJSON,)PRIMARYKEY(id);

Enforce lowercase label values and property names

Because label and property names must bestored as lowercasevalues, do either of the following:

• Enforce the check in application logic.
• Createcheck constraints in theschema.

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

ALTERTABLEGraphNodeADDCONSTRAINTNodeLabelLowerCaseCheckCHECK(LOWER(label)=label);

The following example shows how to add a check constraint to the edge propertyname. The check usesJSON_KEYS toaccess the top-level keys.COALESCEconverts the output to an empty array ifJSON_KEYS returnsNULL and thenchecks that each key is lowercase.

ALTERTABLEGraphEdgeADDCONSTRAINTEdgePropertiesLowerCaseCheckCHECK(NOTarray_includes(COALESCE(JSON_KEYS(properties,1),[]),key->key<>LOWER(key)));

Enforce that properties exist

Create aconstraint that checks if aproperty exists for a label.

In the following example, the constraint checks if aperson node has anameproperty.

ALTERTABLEGraphNodeADDCONSTRAINTNameMustExistForPersonConstraintCHECK(IF(label='person',properties.nameISNOTNULL,TRUE));

Enforce unique properties

Create property-based constraints that check if the property of a node or edgeis unique across nodes or edges with the same label. To do this, use aUNIQUEINDEX against thegeneratedcolumns of properties.

In the following example, the unique index checks that thename andcountryproperties combined are unique for anyperson node.

1. Add a generated column forPersonName.

   ALTERTABLEGraphNodeADDCOLUMNperson_nameSTRING(MAX)AS(IF(label='person',STRING(properties.name),NULL))Hidden;

2. Add a generated column forPersonCountry.

   ALTERTABLEGraphNodeADDCOLUMNperson_countrySTRING(MAX)AS(IF(label='person',STRING(properties.country),NULL))Hidden;

3. Create aNULL_FILTERED unique index against thePersonName andPersonCountry properties.

   CREATEUNIQUENULL_FILTEREDINDEXNameAndCountryMustBeUniqueForPersonONGraphNode(person_name,person_country);

Enforce property data type

Enforce a property data type using a data type constraint on a property valuefor a label, as shown in the following example. This example uses theJSON_TYPEfunction to check that thename property of theperson label uses theSTRING type.

ALTERTABLEGraphNodeADDCONSTRAINTPersonNameMustBeStringTypeConstraintCHECK(IF(label='person',JSON_TYPE(properties.name)='string',TRUE));

CREATEORREPLACEPROPERTYGRAPHFinGraphNODETABLES(GraphNodeLABELEntity-- Defined labelDYNAMICLABEL(label)-- Dynamic label from data column 'label'DYNAMICPROPERTIES(properties));

GRAPHFinGraphMATCH(node:Entity{id:1})-- Querying by the defined labelRETURNnode.name;

• Graph input tables creation
• Property graph creation
• Optional: reverse edge traversal index to boostreverse traversal performance
• Optional: label index to boost performance of queries by labels
• Optional: schema constraints to enforcelowercase labelsandproperty names

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

CREATETABLEGraphNode(idINT64NOTNULL,labelSTRING(MAX)NOTNULL,propertiesJSON)PRIMARYKEY(id);CREATETABLEGraphEdge(idINT64NOTNULL,dest_idINT64NOTNULL,edge_idINT64NOTNULL,labelSTRING(MAX)NOTNULL,propertiesJSON)PRIMARYKEY(id,dest_id,edge_id),INTERLEAVEINPARENTGraphNode;CREATEPROPERTYGRAPHFinGraphNODETABLES(GraphNodeDYNAMICLABEL(label)DYNAMICPROPERTIES(properties))EDGETABLES(GraphEdgeSOURCEKEY(id)REFERENCESGraphNode(id)DESTINATIONKEY(dest_id)REFERENCESGraphNode(id)DYNAMICLABEL(label)DYNAMICPROPERTIES(properties));

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

CREATEINDEXR_EDGEONGraphEdge(dest_id,id,edge_id)STORING(properties),INTERLEAVEINGraphNode;

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

CREATEINDEXIDX_NODE_LABELONGraphNode(label);

The following example adds constraints that enforce lowercase labels andproperties. The last two examples use theJSON_KEYSfunction. Optionally, you can enforce the lowercase check in application logic.

ALTERTABLEGraphNodeADDCONSTRAINTnode_label_lower_caseCHECK(LOWER(label)=label);ALTERTABLEGraphEdgeADDCONSTRAINTedge_label_lower_caseCHECK(LOWER(label)=label);ALTERTABLEGraphNodeADDCONSTRAINTnode_property_keys_lower_caseCHECK(NOTarray_includes(COALESCE(JSON_KEYS(properties,1),[]),key->key<>LOWER(key)));ALTERTABLEGraphEdgeADDCONSTRAINTedge_property_keys_lower_caseCHECK(NOTarray_includes(COALESCE(JSON_KEYS(properties,1),[]),key->key<>LOWER(key)));

Optimize batch updates of dynamic properties with DML

Modifying dynamic properties using functions likeJSON_SET andJSON_REMOVEinvolves read-modify-write operations. This can lead to higher cost compared toupdating properties ofSTRING orINT64 type.

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

• Update multiple rows in a single DML statement rather than processing rowsindividually.

• When updating a wide key range, group and sort the affected rows by primarykeys. Updating non-overlapping ranges with each DML reduces lock contention.

• Usequery parameters inDML statements instead of hard coding them to improve performance.

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

1. @node_ids: Keys ofGraphNode rows, stored in anARRAY parameter. Ifapplicable, grouping and sorting them across DMLs achieves betterperformance.

2. @is_blocked_values: The corresponding values to be updated, stored in anARRAY parameter.

UPDATEGraphNodeSETproperties=JSON_SET(properties,'$.is_blocked',CASEidWHEN@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)WHEREidINUNNEST(@node_ids)

Troubleshoot

This section describes how to troubleshoot issues with schemaless data.

Property appears multiple times in theTO_JSON result

Issue

The following node models thebirthday andname properties as dynamicproperties in itsJSON column. Duplicatebirthday andname propertiesappear in the graph element JSON result.

GRAPHFinGraphMATCH(n:Person{id:14})RETURNSAFE_TO_JSON(n)ASn;

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. UsingTO_JSON orSAFE_TO_JSONto return graph elements results in duplicate properties. This occurs becausetheJSON column (properties) is a schema-defined property, while thefirst-level keys of theJSON are modeled as dynamic properties.

Recommended solution

To avoid this behavior, use thePROPERTIES ALL COLUMNS EXCEPTclause to exclude theproperties column when you define properties in theschema, as shown in the following example:

CREATEORREPLACEPROPERTYGRAPHFinGraphNODETABLES(GraphNodePROPERTIESALLCOLUMNSEXCEPT(properties)DYNAMICLABEL(label)DYNAMICPROPERTIES(properties));

After the schema change, the returned graph elements of theJSON data typedon't have duplicates.

GRAPHFinGraphMATCH(n:Person{id:1})RETURNTO_JSON(n)ASn;

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 aproperty 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, thename propertyis not converted toSTRING type in comparison:

GRAPHFinGraphMATCH(p:Person)WHEREp.name="Alex"RETURNp.id;

Recommended solution

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

GRAPHFinGraphMATCH(p:Person)WHERESTRING(p.name)="Alex"RETURNp.id;

This returns results similar to the following:

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

Alternatively, use aproperty filter to simplifyequality comparisons where value conversion occurs automatically. Notice thatthe value's type ("Alex") must exactly match the property'sSTRING type inJSON.

GRAPHFinGraphMATCH(p:Person{name:'Alex'})RETURNp.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'sused in theRETURN DISTINCT statement:

GRAPHFinGraphMATCH-[t:Transfers]->RETURNDISTINCTt.order_number_strASorder_number_str;

Recommended solution

To fix this issue, use a value conversion beforeRETURN DISTINCT.

GRAPHFinGraphMATCH-[t:Transfers]->RETURNDISTINCTSTRING(t.order_number_str)ASorder_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 usedto group JSON objects:

GRAPHFinGraphMATCH(a:Account)-[t:Transfers]->(b:Account)RETURNt.order_number_str,COUNT(*)AStotal_transfers;

Recommended solution

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

GRAPHFinGraphMATCH(a:Account)-[t:Transfers]->(b:Account)RETURNSTRING(t.order_number_str)ASorder_number_str,COUNT(*)AStotal_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 forordering results:

GRAPHFinGraphMATCH(a:Account)-[t:Transfers]->(b:Account)RETURNa.IdASfrom_account,b.IdASto_account,t.amountORDERBYt.amountDESCLIMIT1;

Recommended solution

To fix this issue, do a conversion ont.amount in theORDER BY clause.

GRAPHFinGraphMATCH(a:Account)-[t:Transfers]->(b:Account)RETURNa.IdASfrom_account,b.IdASto_account,t.amountORDERBYDOUBLE(t.amount)DESCLIMIT1;

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, theorder_number_str property is stored as a JSONSTRING data type. If you try to perform a conversion toINT64, it returns anerror.

GRAPHFinGraphMATCH-[e:Transfers]->WHEREINT64(e.order_number_str)=302290001255747RETURNe.amount;

Recommended solution

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

GRAPHFinGraphMATCH-[e:Transfers]->WHERESTRING(e.order_number_str)="302290001255747"RETURNe.amount;

This returns results similar to the following:

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

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

GRAPHFinGraphMATCH-[e:Transfers]->WHERELAX_INT64(e.order_number_str)=302290001255747RETURNe.amount;

This returns results similar to the following:

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

What's next

• To learn more about JSON, seeModify JSON dataandJSON functions list.
• Compare Spanner Graph and openCypher.
• Migrate to Spanner Graph.