Spanner allows you to create STRUCT
objects from data, as well as to use STRUCT
objects as bound parameters when running a SQL query with one of the
Spanner client libraries.
For more information about the STRUCT
type in Spanner, see Data
types
.
Declaring a user-defined type of STRUCT object
You can declare a STRUCT
object in queries using the syntax described in Declaring a STRUCT
type
.
You can define a type of STRUCT
object as a sequence of field names and their
data types. You can then supply this type along with queries containing STRUCT
-typed parameter bindings and Spanner will use it to check that
the STRUCT
parameter values in your query are valid.
C++
// Cloud Spanner STRUCT<> types are represented by std::tuple<...>. The
// following represents a STRUCT<> with two unnamed STRING fields.
using
NameType
=
std
::
tuple<std
::
string
,
std
::
string
> ;
C#
var
nameType
=
new
SpannerStruct
{
{
"FirstName"
,
SpannerDbType
.
String
,
null
},
{
"LastName"
,
SpannerDbType
.
String
,
null
}
};
Go
type
nameType
struct
{
FirstName
string
LastName
string
}
Java
Type
nameType
=
Type
.
struct
(
Arrays
.
asList
(
StructField
.
of
(
"FirstName"
,
Type
.
string
()),
StructField
.
of
(
"LastName"
,
Type
.
string
())));
Node.js
const
nameType
=
{
type
:
'struct'
,
fields
:
[
{
name
:
'FirstName'
,
type
:
'string'
,
},
{
name
:
'LastName'
,
type
:
'string'
,
},
],
};
PHP
$nameType = new ArrayType(
(new StructType)
->add('FirstName', Database::TYPE_STRING)
->add('LastName', Database::TYPE_STRING)
);
Python
name_type
=
param_types
.
Struct
(
[
param_types
.
StructField
(
"FirstName"
,
param_types
.
STRING
),
param_types
.
StructField
(
"LastName"
,
param_types
.
STRING
),
]
)
Ruby
name_type
=
client
.
fields
FirstName
:
:STRING
,
LastName
:
:STRING
Creating STRUCT objects
The following sample shows how to create STRUCT
objects using the
Spanner client libraries.
C++
// Cloud Spanner STRUCT<> types are represented by std::tuple<...>. The
// following represents a STRUCT<> with two unnamed STRING fields.
using
NameType
=
std
::
tuple<std
::
string
,
std
::
string
> ;
auto
singer_info
=
NameType
{
"Elena"
,
"Campbell"
};
C#
var
nameStruct
=
new
SpannerStruct
{
{
"FirstName"
,
SpannerDbType
.
String
,
"Elena"
},
{
"LastName"
,
SpannerDbType
.
String
,
"Campbell"
},
};
Go
type
name
struct
{
FirstName
string
LastName
string
}
var
singerInfo
=
name
{
"Elena"
,
"Campbell"
}
Java
Struct
name
=
Struct
.
newBuilder
().
set
(
"FirstName"
).
to
(
"Elena"
).
set
(
"LastName"
).
to
(
"Campbell"
).
build
();
Node.js
// Imports the Google Cloud client library
const
{
Spanner
}
=
require
(
' @google-cloud/spanner
'
);
const
nameStruct
=
Spanner
.
struct
({
FirstName
:
'Elena'
,
LastName
:
'Campbell'
,
});
PHP
$nameValue = (new StructValue)
->add('FirstName', 'Elena')
->add('LastName', 'Campbell');
$nameType = (new StructType)
->add('FirstName', Database::TYPE_STRING)
->add('LastName', Database::TYPE_STRING);
Python
record_type
=
param_types
.
Struct
(
[
param_types
.
StructField
(
"FirstName"
,
param_types
.
STRING
),
param_types
.
StructField
(
"LastName"
,
param_types
.
STRING
),
]
)
record_value
=
(
"Elena"
,
"Campbell"
)
Ruby
name_struct
=
{
FirstName
:
"Elena"
,
LastName
:
"Campbell"
}
You can also use the client libraries to create an array of STRUCT
objects, as
seen in the following sample:
C++
// Cloud Spanner STRUCT<> types with named fields are represented by
// std::tuple<std::pair<std::string, T>...>, create an alias to make it easier
// to follow this code.
using
SingerName
=
std
::
tuple<std
::
pair<std
::
string
,
std
::
string
> ,
std
::
pair<std
::
string
,
std
::
string
>> ;
auto
make_name
=
[](
std
::
string
first_name
,
std
::
string
last_name
)
{
return
std
::
make_tuple
(
std
::
make_pair
(
"FirstName"
,
std
::
move
(
first_name
)),
std
::
make_pair
(
"LastName"
,
std
::
move
(
last_name
)));
};
std
::
vector<SingerName>
singer_info
{
make_name
(
"Elena"
,
"Campbell"
),
make_name
(
"Gabriel"
,
"Wright"
),
make_name
(
"Benjamin"
,
"Martinez"
),
};
C#
var
bandMembers
=
new
List<SpannerStruct>
{
new
SpannerStruct
{
{
"FirstName"
,
SpannerDbType
.
String
,
"Elena"
},
{
"LastName"
,
SpannerDbType
.
String
,
"Campbell"
}
},
new
SpannerStruct
{
{
"FirstName"
,
SpannerDbType
.
String
,
"Gabriel"
},
{
"LastName"
,
SpannerDbType
.
String
,
"Wright"
}
},
new
SpannerStruct
{
{
"FirstName"
,
SpannerDbType
.
String
,
"Benjamin"
},
{
"LastName"
,
SpannerDbType
.
String
,
"Martinez"
}
},
};
Go
var
bandMembers
=
[]
nameType
{
{
"Elena"
,
"Campbell"
},
{
"Gabriel"
,
"Wright"
},
{
"Benjamin"
,
"Martinez"
},
}
Java
List<Struct>
bandMembers
=
new
ArrayList
<> ();
bandMembers
.
add
(
Struct
.
newBuilder
().
set
(
"FirstName"
).
to
(
"Elena"
).
set
(
"LastName"
).
to
(
"Campbell"
).
build
());
bandMembers
.
add
(
Struct
.
newBuilder
().
set
(
"FirstName"
).
to
(
"Gabriel"
).
set
(
"LastName"
).
to
(
"Wright"
).
build
());
bandMembers
.
add
(
Struct
.
newBuilder
().
set
(
"FirstName"
).
to
(
"Benjamin"
).
set
(
"LastName"
).
to
(
"Martinez"
).
build
());
Node.js
const
bandMembersType
=
{
type
:
'array'
,
child
:
nameType
,
};
const
bandMembers
=
[
Spanner
.
struct
({
FirstName
:
'Elena'
,
LastName
:
'Campbell'
,
}),
Spanner
.
struct
({
FirstName
:
'Gabriel'
,
LastName
:
'Wright'
,
}),
Spanner
.
struct
({
FirstName
:
'Benjamin'
,
LastName
:
'Martinez'
,
}),
];
PHP
$bandMembers = [
(new StructValue)
->add('FirstName', 'Elena')
->add('LastName', 'Campbell'),
(new StructValue)
->add('FirstName', 'Gabriel')
->add('LastName', 'Wright'),
(new StructValue)
->add('FirstName', 'Benjamin')
->add('LastName', 'Martinez')
];
Python
band_members
=
[
(
"Elena"
,
"Campbell"
),
(
"Gabriel"
,
"Wright"
),
(
"Benjamin"
,
"Martinez"
),
]
Ruby
band_members
=
[
name_type
.
struct
(
[
"Elena"
,
"Campbell"
]
),
name_type
.
struct
(
[
"Gabriel"
,
"Wright"
]
),
name_type
.
struct
(
[
"Benjamin"
,
"Martinez"
]
)
]
Returning STRUCT objects in SQL query results
A Spanner SQL query can return an array of STRUCT
objects as a column
for certain queries. For more information, see Using STRUCTS with SELECT
.
Using STRUCT objects as bound parameters in SQL queries
You can use STRUCT
objects as bound parameters in a SQL query. For more
information about parameters, see Query parameters
.
Querying data with a STRUCT object
The following sample shows how to bind values in a STRUCT
object to
parameters in a SQL query statement, execute the query, and output the results.
C++
void
QueryDataWithStruct
(
google
::
cloud
::
spanner
::
Client
client
)
{
namespace
spanner
=
::
google
::
cloud
::
spanner
;
// Cloud Spanner STRUCT<> types are represented by std::tuple<...>. The
// following represents a STRUCT<> with two unnamed STRING fields.
using
NameType
=
std
::
tuple<std
::
string
,
std
::
string
> ;
auto
singer_info
=
NameType
{
"Elena"
,
"Campbell"
};
auto
rows
=
client
.
ExecuteQuery
(
spanner
::
SqlStatement
(
"SELECT SingerId FROM Singers WHERE (FirstName, LastName) = @name"
,
{{"name", spanner::Value(singer_info)} }));
for
(
auto
&
row
:
spanner
::
StreamOf<std
::
tuple<std
::
int64_t
>> (
rows
))
{
if
(
!
row
)
throw
std
::
move
(
row
).
status
();
std
::
cout
<<
"SingerId: "
<<
std
::
get<0>
(
*
row
)
<<
"
\n
"
;
}
std
::
cout
<<
"Query completed for [spanner_query_data_with_struct]
\n
"
;
}
C#
using
Google.Cloud.Spanner.Data
;
using
System.Collections.Generic
;
using
System.Threading.Tasks
;
public
class
QueryDataWithStructAsyncSample
{
public
async
Task<List<int>
>
QueryDataWithStructAsync
(
string
projectId
,
string
instanceId
,
string
databaseId
)
{
var
nameStruct
=
new
SpannerStruct
{
{
"FirstName"
,
SpannerDbType
.
String
,
"Elena"
},
{
"LastName"
,
SpannerDbType
.
String
,
"Campbell"
},
};
string
connectionString
=
$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}"
;
var
singerIds
=
new
List<int>
();
using
var
connection
=
new
SpannerConnection
(
connectionString
);
using
var
cmd
=
connection
.
CreateSelectCommand
(
"SELECT SingerId FROM Singers "
+
"WHERE STRUCT<FirstName STRING, LastName STRING>"
+
"(FirstName, LastName) = @name"
);
cmd
.
Parameters
.
Add
(
"name"
,
nameStruct
.
GetSpannerDbType
(),
nameStruct
);
using
var
reader
=
await
cmd
.
ExecuteReaderAsync
();
while
(
await
reader
.
ReadAsync
())
{
singerIds
.
Add
(
reader
.
GetFieldValue<int>
(
"SingerId"
));
}
return
singerIds
;
}
}
Go
stmt
:=
spanner
.
Statement
{
SQL
:
`SELECT SingerId FROM SINGERS
WHERE (FirstName, LastName) = @singerinfo`
,
Params
:
map
[
string
]
interface
{}{
"singerinfo"
:
singerInfo
},
}
iter
:=
client
.
Single
().
Query
(
ctx
,
stmt
)
defer
iter
.
Stop
()
for
{
row
,
err
:=
iter
.
Next
()
if
err
==
iterator
.
Done
{
return
nil
}
if
err
!=
nil
{
return
err
}
var
singerID
int64
if
err
:=
row
.
Columns
(
& singerID
);
err
!=
nil
{
return
err
}
fmt
.
Fprintf
(
w
,
"%d\n"
,
singerID
)
}
Java
Statement
s
=
Statement
.
newBuilder
(
"SELECT SingerId FROM Singers "
+
"WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) "
+
"= @name"
)
.
bind
(
"name"
)
.
to
(
name
)
.
build
();
try
(
ResultSet
resultSet
=
dbClient
.
singleUse
().
executeQuery
(
s
))
{
while
(
resultSet
.
next
())
{
System
.
out
.
printf
(
"%d\n"
,
resultSet
.
getLong
(
"SingerId"
));
}
}
Node.js
/**
* TODO(developer): Uncomment the following lines before running the sample.
*/
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// Creates a client
const
spanner
=
new
Spanner
({
projectId
:
projectId
,
});
// Gets a reference to a Cloud Spanner instance and database
const
instance
=
spanner
.
instance
(
instanceId
);
const
database
=
instance
.
database
(
databaseId
);
const
query
=
{
sql
:
'SELECT SingerId FROM Singers WHERE '
+
'STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) = @name'
,
params
:
{
name
:
nameStruct
,
},
};
// Queries rows from the Singers table
try
{
const
[
rows
]
=
await
database
.
run
(
query
);
rows
.
forEach
(
row
=
>
{
const
json
=
row
.
toJSON
();
console
.
log
(
`SingerId:
${
json
.
SingerId
}
`
);
});
}
catch
(
err
)
{
console
.
error
(
'ERROR:'
,
err
);
}
finally
{
// Close the database when finished.
database
.
close
();
}
PHP
$results = $database->execute(
'SELECT SingerId FROM Singers ' .
'WHERE STRUCT<FirstName STRING, LastName STRING>' .
'(FirstName, LastName) = @name',
[
'parameters' => [
'name' => $nameValue
],
'types' => [
'name' => $nameType
]
]
);
foreach ($results as $row) {
printf('SingerId: %s' . PHP_EOL,
$row['SingerId']);
}
Python
spanner_client
=
spanner
.
Client
()
instance
=
spanner_client
.
instance
(
instance_id
)
database
=
instance
.
database
(
database_id
)
with
database
.
snapshot
()
as
snapshot
:
results
=
snapshot
.
execute_sql
(
"SELECT SingerId FROM Singers WHERE "
"(FirstName, LastName) = @name"
,
params
=
{
"name"
:
record_value
},
param_types
=
{
"name"
:
record_type
},
)
for
row
in
results
:
print
(
"SingerId:
{}
"
.
format
(
*
row
))
Ruby
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require
"google/cloud/spanner"
spanner
=
Google
::
Cloud
::
Spanner
.
new
project
:
project_id
client
=
spanner
.
client
instance_id
,
database_id
client
.
execute
(
"SELECT SingerId FROM Singers WHERE "
+
"(FirstName, LastName) = @name"
,
params
:
{
name
:
name_struct
}
)
.
rows
.
each
do
|
row
|
puts
row
[
:SingerId
]
end
Querying data with an array of STRUCT objects
The following sample shows how to execute a query that uses an array of STRUCT
objects. Use the UNNEST
operator to flatten an array of STRUCT
objects
into rows:
C++
void
QueryDataWithArrayOfStruct
(
google
::
cloud
::
spanner
::
Client
client
)
{
namespace
spanner
=
::
google
::
cloud
::
spanner
;
// Cloud Spanner STRUCT<> types with named fields are represented by
// std::tuple<std::pair<std::string, T>...>, create an alias to make it easier
// to follow this code.
using
SingerName
=
std
::
tuple<std
::
pair<std
::
string
,
std
::
string
> ,
std
::
pair<std
::
string
,
std
::
string
>> ;
auto
make_name
=
[](
std
::
string
first_name
,
std
::
string
last_name
)
{
return
std
::
make_tuple
(
std
::
make_pair
(
"FirstName"
,
std
::
move
(
first_name
)),
std
::
make_pair
(
"LastName"
,
std
::
move
(
last_name
)));
};
std
::
vector<SingerName>
singer_info
{
make_name
(
"Elena"
,
"Campbell"
),
make_name
(
"Gabriel"
,
"Wright"
),
make_name
(
"Benjamin"
,
"Martinez"
),
};
auto
rows
=
client
.
ExecuteQuery
(
spanner
::
SqlStatement
(
"SELECT SingerId FROM Singers"
" WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName)"
" IN UNNEST(@names)"
,
{{"names", spanner::Value(singer_info)} }));
for
(
auto
&
row
:
spanner
::
StreamOf<std
::
tuple<std
::
int64_t
>> (
rows
))
{
if
(
!
row
)
throw
std
::
move
(
row
).
status
();
std
::
cout
<<
"SingerId: "
<<
std
::
get<0>
(
*
row
)
<<
"
\n
"
;
}
std
::
cout
<<
"Query completed for"
<<
" [spanner_query_data_with_array_of_struct]
\n
"
;
}
C#
using
Google.Cloud.Spanner.Data
;
using
System.Collections.Generic
;
using
System.Threading.Tasks
;
public
class
QueryDataWithArrayOfStructAsyncSample
{
public
async
Task<List<int>
>
QueryDataWithArrayOfStructAsync
(
string
projectId
,
string
instanceId
,
string
databaseId
)
{
var
nameType
=
new
SpannerStruct
{
{
"FirstName"
,
SpannerDbType
.
String
,
null
},
{
"LastName"
,
SpannerDbType
.
String
,
null
}
};
var
bandMembers
=
new
List<SpannerStruct>
{
new
SpannerStruct
{
{
"FirstName"
,
SpannerDbType
.
String
,
"Elena"
},
{
"LastName"
,
SpannerDbType
.
String
,
"Campbell"
}
},
new
SpannerStruct
{
{
"FirstName"
,
SpannerDbType
.
String
,
"Gabriel"
},
{
"LastName"
,
SpannerDbType
.
String
,
"Wright"
}
},
new
SpannerStruct
{
{
"FirstName"
,
SpannerDbType
.
String
,
"Benjamin"
},
{
"LastName"
,
SpannerDbType
.
String
,
"Martinez"
}
},
};
var
singerIds
=
new
List<int>
();
string
connectionString
=
$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}"
;
using
var
connection
=
new
SpannerConnection
(
connectionString
);
using
var
cmd
=
connection
.
CreateSelectCommand
(
"SELECT SingerId FROM Singers WHERE STRUCT<FirstName STRING, LastName STRING> "
+
"(FirstName, LastName) IN UNNEST(@names)"
);
cmd
.
Parameters
.
Add
(
"names"
,
SpannerDbType
.
ArrayOf
(
nameType
.
GetSpannerDbType
()),
bandMembers
);
using
var
reader
=
await
cmd
.
ExecuteReaderAsync
();
while
(
await
reader
.
ReadAsync
())
{
singerIds
.
Add
(
reader
.
GetFieldValue<int>
(
"SingerId"
));
}
return
singerIds
;
}
}
Go
stmt
:=
spanner
.
Statement
{
SQL
:
`SELECT SingerId FROM SINGERS
WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName)
IN UNNEST(@names)`
,
Params
:
map
[
string
]
interface
{}{
"names"
:
bandMembers
},
}
iter
:=
client
.
Single
().
Query
(
ctx
,
stmt
)
defer
iter
.
Stop
()
for
{
row
,
err
:=
iter
.
Next
()
if
err
==
iterator
.
Done
{
return
nil
}
if
err
!=
nil
{
return
err
}
var
singerID
int64
if
err
:=
row
.
Columns
(
& singerID
);
err
!=
nil
{
return
err
}
fmt
.
Fprintf
(
w
,
"%d\n"
,
singerID
)
}
Java
Statement
s
=
Statement
.
newBuilder
(
"SELECT SingerId FROM Singers WHERE "
+
"STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) "
+
"IN UNNEST(@names) "
+
"ORDER BY SingerId DESC"
)
.
bind
(
"names"
)
.
toStructArray
(
nameType
,
bandMembers
)
.
build
();
try
(
ResultSet
resultSet
=
dbClient
.
singleUse
().
executeQuery
(
s
))
{
while
(
resultSet
.
next
())
{
System
.
out
.
printf
(
"%d\n"
,
resultSet
.
getLong
(
"SingerId"
));
}
}
Node.js
const
query
=
{
sql
:
'SELECT SingerId FROM Singers '
+
'WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) '
+
'IN UNNEST(@names) '
+
'ORDER BY SingerId'
,
params
:
{
names
:
bandMembers
,
},
types
:
{
names
:
bandMembersType
,
},
};
// Queries rows from the Singers table
try
{
const
[
rows
]
=
await
database
.
run
(
query
);
rows
.
forEach
(
row
=
>
{
const
json
=
row
.
toJSON
();
console
.
log
(
`SingerId:
${
json
.
SingerId
}
`
);
});
}
catch
(
err
)
{
console
.
error
(
'ERROR:'
,
err
);
}
finally
{
// Close the database when finished.
database
.
close
();
}
PHP
$results = $database->execute(
'SELECT SingerId FROM Singers ' .
'WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) ' .
'IN UNNEST(@names)',
[
'parameters' => [
'names' => $bandMembers
],
'types' => [
'names' => $nameType
]
]
);
foreach ($results as $row) {
printf('SingerId: %s' . PHP_EOL,
$row['SingerId']);
}
Python
spanner_client
=
spanner
.
Client
()
instance
=
spanner_client
.
instance
(
instance_id
)
database
=
instance
.
database
(
database_id
)
with
database
.
snapshot
()
as
snapshot
:
results
=
snapshot
.
execute_sql
(
"SELECT SingerId FROM Singers WHERE "
"STRUCT<FirstName STRING, LastName STRING>"
"(FirstName, LastName) IN UNNEST(@names)"
,
params
=
{
"names"
:
band_members
},
param_types
=
{
"names"
:
param_types
.
Array
(
name_type
)},
)
for
row
in
results
:
print
(
"SingerId:
{}
"
.
format
(
*
row
))
Ruby
client
.
execute
(
"SELECT SingerId FROM Singers WHERE "
+
"STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) IN UNNEST(@names)"
,
params
:
{
names
:
band_members
}
)
.
rows
.
each
do
|
row
|
puts
row
[
:SingerId
]
end
Modifying data with DML
The following code example uses a STRUCT
with bound parameters and Data
Manipulation Language (DML) to update a single value in rows that match the
WHERE clause condition. For rows where the FirstName
is Timothy
and the LastName
is Campbell
, the LastName
is updated to Grant
.
C++
void
DmlStructs
(
google
::
cloud
::
spanner
::
Client
client
)
{
namespace
spanner
=
::
google
::
cloud
::
spanner
;
std
::
int64_t
rows_modified
=
0
;
auto
commit_result
=
client
.
Commit
([
& client
,
& rows_modified
](
spanner
::
Transaction
const
&
txn
)
-
>
google
::
cloud
::
StatusOr<spanner
::
Mutations
>
{
auto
singer_info
=
std
::
make_tuple
(
"Marc"
,
"Richards"
);
auto
sql
=
spanner
::
SqlStatement
(
"UPDATE Singers SET FirstName = 'Keith' WHERE "
"STRUCT<FirstName String, LastName String>(FirstName, LastName) "
"= @name"
,
{{"name", spanner::Value(std::move(singer_info))} });
auto
dml_result
=
client
.
ExecuteDml
(
txn
,
std
::
move
(
sql
));
if
(
!
dml_result
)
return
std
::
move
(
dml_result
).
status
();
rows_modified
=
dml_result
-
> RowsModified
();
return
spanner
::
Mutations
{};
});
if
(
!
commit_result
)
throw
std
::
move
(
commit_result
).
status
();
std
::
cout
<<
rows_modified
<<
" update was successful [spanner_dml_structs]
\n
"
;
}
C#
using
Google.Cloud.Spanner.Data
;
using
System
;
using
System.Threading.Tasks
;
public
class
UpdateUsingDmlWithStructCoreAsyncSample
{
public
async
Task<int>
UpdateUsingDmlWithStructCoreAsync
(
string
projectId
,
string
instanceId
,
string
databaseId
)
{
var
nameStruct
=
new
SpannerStruct
{
{
"FirstName"
,
SpannerDbType
.
String
,
"Timothy"
},
{
"LastName"
,
SpannerDbType
.
String
,
"Campbell"
}
};
string
connectionString
=
$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}"
;
using
var
connection
=
new
SpannerConnection
(
connectionString
);
await
connection
.
OpenAsync
();
using
var
cmd
=
connection
.
CreateDmlCommand
(
"UPDATE Singers SET LastName = 'Grant' WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) = @name"
);
cmd
.
Parameters
.
Add
(
"name"
,
nameStruct
.
GetSpannerDbType
(),
nameStruct
);
int
rowCount
=
await
cmd
.
ExecuteNonQueryAsync
();
Console
.
WriteLine
(
$"{rowCount} row(s) updated..."
);
return
rowCount
;
}
}
Go
import
(
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
)
func
updateUsingDMLStruct
(
w
io
.
Writer
,
db
string
)
error
{
ctx
:=
context
.
Background
()
client
,
err
:=
spanner
.
NewClient
(
ctx
,
db
)
if
err
!=
nil
{
return
err
}
defer
client
.
Close
()
_
,
err
=
client
.
ReadWriteTransaction
(
ctx
,
func
(
ctx
context
.
Context
,
txn
*
spanner
.
ReadWriteTransaction
)
error
{
type
name
struct
{
FirstName
string
LastName
string
}
var
singerInfo
=
name
{
"Timothy"
,
"Campbell"
}
stmt
:=
spanner
.
Statement
{
SQL
:
`Update Singers Set LastName = 'Grant'
WHERE STRUCT<FirstName String, LastName String>(Firstname, LastName) = @name`
,
Params
:
map
[
string
]
interface
{}{
"name"
:
singerInfo
},
}
rowCount
,
err
:=
txn
.
Update
(
ctx
,
stmt
)
if
err
!=
nil
{
return
err
}
fmt
.
Fprintf
(
w
,
"%d record(s) inserted.\n"
,
rowCount
)
return
nil
})
return
err
}
Java
static
void
updateUsingDmlWithStruct
(
DatabaseClient
dbClient
)
{
Struct
name
=
Struct
.
newBuilder
().
set
(
"FirstName"
).
to
(
"Timothy"
).
set
(
"LastName"
).
to
(
"Campbell"
).
build
();
Statement
s
=
Statement
.
newBuilder
(
"UPDATE Singers SET LastName = 'Grant' "
+
"WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) "
+
"= @name"
)
.
bind
(
"name"
)
.
to
(
name
)
.
build
();
dbClient
.
readWriteTransaction
()
.
run
(
transaction
-
>
{
long
rowCount
=
transaction
.
executeUpdate
(
s
);
System
.
out
.
printf
(
"%d record updated.\n"
,
rowCount
);
return
null
;
});
}
Node.js
// Imports the Google Cloud client library
const
{
Spanner
}
=
require
(
' @google-cloud/spanner
'
);
const
nameStruct
=
Spanner
.
struct
({
FirstName
:
'Timothy'
,
LastName
:
'Campbell'
,
});
/**
* TODO(developer): Uncomment the following lines before running the sample.
*/
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// Creates a client
const
spanner
=
new
Spanner
({
projectId
:
projectId
,
});
// Gets a reference to a Cloud Spanner instance and database
const
instance
=
spanner
.
instance
(
instanceId
);
const
database
=
instance
.
database
(
databaseId
);
database
.
runTransaction
(
async
(
err
,
transaction
)
=
>
{
if
(
err
)
{
console
.
error
(
err
);
return
;
}
try
{
const
[
rowCount
]
=
await
transaction
.
runUpdate
({
sql
:
`UPDATE Singers SET LastName = 'Grant'
WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) = @name`
,
params
:
{
name
:
nameStruct
,
},
});
console
.
log
(
`Successfully updated
${
rowCount
}
record.`
);
await
transaction
.
commit
();
}
catch
(
err
)
{
console
.
error
(
'ERROR:'
,
err
);
}
finally
{
// Close the database when finished.
database
.
close
();
}
});
PHP
use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Database;
use Google\Cloud\Spanner\Transaction;
use Google\Cloud\Spanner\StructType;
use Google\Cloud\Spanner\StructValue;
/**
* Update data with a DML statement using Structs.
*
* The database and table must already exist and can be created using
* `create_database`.
* Example:
* ```
* insert_data($instanceId, $databaseId);
* ```
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function update_data_with_dml_structs(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$database->runTransaction(function (Transaction $t) {
$nameValue = (new StructValue)
->add('FirstName', 'Timothy')
->add('LastName', 'Campbell');
$nameType = (new StructType)
->add('FirstName', Database::TYPE_STRING)
->add('LastName', Database::TYPE_STRING);
$rowCount = $t->executeUpdate(
"UPDATE Singers SET LastName = 'Grant' "
. 'WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) '
. '= @name',
[
'parameters' => [
'name' => $nameValue
],
'types' => [
'name' => $nameType
]
]);
$t->commit();
printf('Updated %d row(s).' . PHP_EOL, $rowCount);
});
}
Python
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client
=
spanner
.
Client
()
instance
=
spanner_client
.
instance
(
instance_id
)
database
=
instance
.
database
(
database_id
)
record_type
=
param_types
.
Struct
(
[
param_types
.
StructField
(
"FirstName"
,
param_types
.
STRING
),
param_types
.
StructField
(
"LastName"
,
param_types
.
STRING
),
]
)
record_value
=
(
"Timothy"
,
"Campbell"
)
def
write_with_struct
(
transaction
):
row_ct
=
transaction
.
execute_update
(
"UPDATE Singers SET LastName = 'Grant' "
"WHERE STRUCT<FirstName STRING, LastName STRING>"
"(FirstName, LastName) = @name"
,
params
=
{
"name"
:
record_value
},
param_types
=
{
"name"
:
record_type
},
)
print
(
"
{}
record(s) updated."
.
format
(
row_ct
))
database
.
run_in_transaction
(
write_with_struct
)
Ruby
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require
"google/cloud/spanner"
spanner
=
Google
::
Cloud
::
Spanner
.
new
project
:
project_id
client
=
spanner
.
client
instance_id
,
database_id
row_count
=
0
name_struct
=
{
FirstName
:
"Timothy"
,
LastName
:
"Campbell"
}
client
.
transaction
do
|
transaction
|
row_count
=
transaction
.
execute_update
(
"UPDATE Singers SET LastName = 'Grant'
WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) = @name"
,
params
:
{
name
:
name_struct
}
)
end
puts
"
#{
row_count
}
record updated."
Accessing STRUCT field values
You can access fields inside a STRUCT
object by name.
C++
void
FieldAccessOnStructParameters
(
google
::
cloud
::
spanner
::
Client
client
)
{
namespace
spanner
=
::
google
::
cloud
::
spanner
;
// Cloud Spanner STRUCT<> with named fields is represented as
// tuple<pair<string, T>...>. Create a type alias for this example:
using
SingerName
=
std
::
tuple<std
::
pair<std
::
string
,
std
::
string
> ,
std
::
pair<std
::
string
,
std
::
string
>> ;
SingerName
name
({
"FirstName"
,
"Elena"
},
{
"LastName"
,
"Campbell"
});
auto
rows
=
client
.
ExecuteQuery
(
spanner
::
SqlStatement
(
"SELECT SingerId FROM Singers WHERE FirstName = @name.FirstName"
,
{{"name", spanner::Value(name)} }));
for
(
auto
&
row
:
spanner
::
StreamOf<std
::
tuple<std
::
int64_t
>> (
rows
))
{
if
(
!
row
)
throw
std
::
move
(
row
).
status
();
std
::
cout
<<
"SingerId: "
<<
std
::
get<0>
(
*
row
)
<<
"
\n
"
;
}
std
::
cout
<<
"Query completed for"
<<
" [spanner_field_access_on_struct_parameters]
\n
"
;
}
C#
using
Google.Cloud.Spanner.Data
;
using
System.Collections.Generic
;
using
System.Threading.Tasks
;
public
class
QueryDataWithStructFieldAsyncSample
{
public
async
Task<List<int>
>
QueryDataWithStructFieldAsync
(
string
projectId
,
string
instanceId
,
string
databaseId
)
{
string
connectionString
=
$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}"
;
var
structParam
=
new
SpannerStruct
{
{
"FirstName"
,
SpannerDbType
.
String
,
"Elena"
},
{
"LastName"
,
SpannerDbType
.
String
,
"Campbell"
},
};
var
singerIds
=
new
List<int>
();
using
var
connection
=
new
SpannerConnection
(
connectionString
);
using
var
cmd
=
connection
.
CreateSelectCommand
(
"SELECT SingerId FROM Singers WHERE FirstName = @name.FirstName"
);
cmd
.
Parameters
.
Add
(
"name"
,
structParam
.
GetSpannerDbType
(),
structParam
);
using
var
reader
=
await
cmd
.
ExecuteReaderAsync
();
while
(
await
reader
.
ReadAsync
())
{
singerIds
.
Add
(
reader
.
GetFieldValue<int>
(
"SingerId"
));
}
return
singerIds
;
}
}
Go
import
(
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
"google.golang.org/api/iterator"
)
func
queryWithStructField
(
w
io
.
Writer
,
db
string
)
error
{
ctx
:=
context
.
Background
()
client
,
err
:=
spanner
.
NewClient
(
ctx
,
db
)
if
err
!=
nil
{
return
err
}
defer
client
.
Close
()
type
structParam
struct
{
FirstName
string
LastName
string
}
var
singerInfo
=
structParam
{
"Elena"
,
"Campbell"
}
stmt
:=
spanner
.
Statement
{
SQL
:
`SELECT SingerId FROM SINGERS
WHERE FirstName = @name.FirstName`
,
Params
:
map
[
string
]
interface
{}{
"name"
:
singerInfo
},
}
iter
:=
client
.
Single
().
Query
(
ctx
,
stmt
)
defer
iter
.
Stop
()
for
{
row
,
err
:=
iter
.
Next
()
if
err
==
iterator
.
Done
{
return
nil
}
if
err
!=
nil
{
return
err
}
var
singerID
int64
if
err
:=
row
.
Columns
(
& singerID
);
err
!=
nil
{
return
err
}
fmt
.
Fprintf
(
w
,
"%d\n"
,
singerID
)
}
}
Java
static
void
queryStructField
(
DatabaseClient
dbClient
)
{
Statement
s
=
Statement
.
newBuilder
(
"SELECT SingerId FROM Singers WHERE FirstName = @name.FirstName"
)
.
bind
(
"name"
)
.
to
(
Struct
.
newBuilder
()
.
set
(
"FirstName"
)
.
to
(
"Elena"
)
.
set
(
"LastName"
)
.
to
(
"Campbell"
)
.
build
())
.
build
();
try
(
ResultSet
resultSet
=
dbClient
.
singleUse
().
executeQuery
(
s
))
{
while
(
resultSet
.
next
())
{
System
.
out
.
printf
(
"%d\n"
,
resultSet
.
getLong
(
"SingerId"
));
}
}
}
Node.js
// Imports the Google Cloud client library
const
{
Spanner
}
=
require
(
' @google-cloud/spanner
'
);
/**
* TODO(developer): Uncomment the following lines before running the sample.
*/
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// Creates a client
const
spanner
=
new
Spanner
({
projectId
:
projectId
,
});
// Gets a reference to a Cloud Spanner instance and database
const
instance
=
spanner
.
instance
(
instanceId
);
const
database
=
instance
.
database
(
databaseId
);
const
nameStruct
=
Spanner
.
struct
({
FirstName
:
'Elena'
,
LastName
:
'Campbell'
,
});
const
query
=
{
sql
:
'SELECT SingerId FROM Singers WHERE FirstName = @name.FirstName'
,
params
:
{
name
:
nameStruct
,
},
};
// Queries rows from the Singers table
try
{
const
[
rows
]
=
await
database
.
run
(
query
);
rows
.
forEach
(
row
=
>
{
const
json
=
row
.
toJSON
();
console
.
log
(
`SingerId:
${
json
.
SingerId
}
`
);
});
}
catch
(
err
)
{
console
.
error
(
'ERROR:'
,
err
);
}
finally
{
// Close the database when finished.
database
.
close
();
}
PHP
use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Database;
use Google\Cloud\Spanner\StructType;
/**
* Queries sample data from the database using a struct field value.
* Example:
* ```
* query_data_with_struct_field($instanceId, $databaseId);
* ```
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function query_data_with_struct_field(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$nameType = (new StructType)
->add('FirstName', Database::TYPE_STRING)
->add('LastName', Database::TYPE_STRING);
$results = $database->execute(
'SELECT SingerId FROM Singers WHERE FirstName = @name.FirstName',
[
'parameters' => [
'name' => [
'FirstName' => 'Elena',
'LastName' => 'Campbell'
]
],
'types' => [
'name' => $nameType
]
]
);
foreach ($results as $row) {
printf('SingerId: %s' . PHP_EOL,
$row['SingerId']);
}
}
Python
def
query_struct_field
(
instance_id
,
database_id
):
"""Query a table using field access on a STRUCT parameter."""
spanner_client
=
spanner
.
Client
()
instance
=
spanner_client
.
instance
(
instance_id
)
database
=
instance
.
database
(
database_id
)
name_type
=
param_types
.
Struct
(
[
param_types
.
StructField
(
"FirstName"
,
param_types
.
STRING
),
param_types
.
StructField
(
"LastName"
,
param_types
.
STRING
),
]
)
with
database
.
snapshot
()
as
snapshot
:
results
=
snapshot
.
execute_sql
(
"SELECT SingerId FROM Singers "
"WHERE FirstName = @name.FirstName"
,
params
=
{
"name"
:
(
"Elena"
,
"Campbell"
)},
param_types
=
{
"name"
:
name_type
},
)
for
row
in
results
:
print
(
"SingerId:
{}
"
.
format
(
*
row
))
Ruby
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require
"google/cloud/spanner"
spanner
=
Google
::
Cloud
::
Spanner
.
new
project
:
project_id
client
=
spanner
.
client
instance_id
,
database_id
name_struct
=
{
FirstName
:
"Elena"
,
LastName
:
"Campbell"
}
client
.
execute
(
"SELECT SingerId FROM Singers WHERE FirstName = @name.FirstName"
,
params
:
{
name
:
name_struct
}
)
.
rows
.
each
do
|
row
|
puts
row
[
:SingerId
]
end
You can even have fields of STRUCT
or ARRAY<STRUCT>
type inside STRUCT
values and access them similarly:
C++
void
FieldAccessOnNestedStruct
(
google
::
cloud
::
spanner
::
Client
client
)
{
namespace
spanner
=
::
google
::
cloud
::
spanner
;
// Cloud Spanner STRUCT<> with named fields is represented as
// tuple<pair<string, T>...>. Create a type alias for this example:
using
SingerFullName
=
std
::
tuple<std
::
pair<std
::
string
,
std
::
string
> ,
std
::
pair<std
::
string
,
std
::
string
>> ;
auto
make_name
=
[](
std
::
string
fname
,
std
::
string
lname
)
{
return
SingerFullName
({
"FirstName"
,
std
::
move
(
fname
)},
{
"LastName"
,
std
::
move
(
lname
)});
};
using
SongInfo
=
std
::
tuple<std
::
pair<std
::
string
,
std
::
string
> ,
std
::
pair<std
::
string
,
std
::
vector<SingerFullName>
>> ;
auto
songinfo
=
SongInfo
(
{
"SongName"
,
"Imagination"
},
{
"ArtistNames"
,
{
make_name
(
"Elena"
,
"Campbell"
),
make_name
(
"Hannah"
,
"Harris"
)}});
auto
rows
=
client
.
ExecuteQuery
(
spanner
::
SqlStatement
(
"SELECT SingerId, @songinfo.SongName FROM Singers"
" WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName)"
" IN UNNEST(@songinfo.ArtistNames)"
,
{{"songinfo", spanner::Value(songinfo)} }));
using
RowType
=
std
::
tuple<std
::
int64_t
,
std
::
string
> ;
for
(
auto
&
row
:
spanner
::
StreamOf<RowType>
(
rows
))
{
if
(
!
row
)
throw
std
::
move
(
row
).
status
();
std
::
cout
<<
"SingerId: "
<<
std
::
get<0>
(
*
row
)
<<
" SongName: "
<<
std
::
get<1>
(
*
row
)
<<
"
\n
"
;
}
std
::
cout
<<
"Query completed for [spanner_field_access_on_nested_struct]
\n
"
;
}
C#
using
Google.Cloud.Spanner.Data
;
using
System
;
using
System.Collections.Generic
;
using
System.Threading.Tasks
;
public
class
QueryDataWithNestedStructFieldAsyncSample
{
public
async
Task<List<int>
>
QueryDataWithNestedStructFieldAsync
(
string
projectId
,
string
instanceId
,
string
databaseId
)
{
string
connectionString
=
$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}"
;
SpannerStruct
name1
=
new
SpannerStruct
{
{
"FirstName"
,
SpannerDbType
.
String
,
"Elena"
},
{
"LastName"
,
SpannerDbType
.
String
,
"Campbell"
}
};
SpannerStruct
name2
=
new
SpannerStruct
{
{
"FirstName"
,
SpannerDbType
.
String
,
"Hannah"
},
{
"LastName"
,
SpannerDbType
.
String
,
"Harris"
}
};
SpannerStruct
songInfo
=
new
SpannerStruct
{
{
"song_name"
,
SpannerDbType
.
String
,
"Imagination"
},
{
"artistNames"
,
SpannerDbType
.
ArrayOf
(
name1
.
GetSpannerDbType
()),
new
[]
{
name1
,
name2
}
}
};
var
singerIds
=
new
List<int>
();
using
var
connection
=
new
SpannerConnection
(
connectionString
);
using
var
cmd
=
connection
.
CreateSelectCommand
(
"SELECT SingerId, @song_info.song_name "
+
"FROM Singers WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) "
+
"IN UNNEST(@song_info.artistNames)"
);
cmd
.
Parameters
.
Add
(
"song_info"
,
songInfo
.
GetSpannerDbType
(),
songInfo
);
using
var
reader
=
await
cmd
.
ExecuteReaderAsync
();
while
(
await
reader
.
ReadAsync
())
{
var
singerId
=
reader
.
GetFieldValue<int>
(
"SingerId"
);
singerIds
.
Add
(
singerId
);
Console
.
WriteLine
(
$"SingerId: {singerId}"
);
Console
.
WriteLine
(
$"Song Name: {reader.GetFieldValue<string>(1)}"
);
}
return
singerIds
;
}
}
Go
import
(
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
"google.golang.org/api/iterator"
)
func
queryWithNestedStructField
(
w
io
.
Writer
,
db
string
)
error
{
ctx
:=
context
.
Background
()
client
,
err
:=
spanner
.
NewClient
(
ctx
,
db
)
if
err
!=
nil
{
return
err
}
defer
client
.
Close
()
type
nameType
struct
{
FirstName
string
LastName
string
}
type
songInfoStruct
struct
{
SongName
string
ArtistNames
[]
nameType
}
var
songInfo
=
songInfoStruct
{
SongName
:
"Imagination"
,
ArtistNames
:
[]
nameType
{
{
FirstName
:
"Elena"
,
LastName
:
"Campbell"
},
{
FirstName
:
"Hannah"
,
LastName
:
"Harris"
},
},
}
stmt
:=
spanner
.
Statement
{
SQL
:
`SELECT SingerId, @songinfo.SongName FROM Singers
WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName)
IN UNNEST(@songinfo.ArtistNames)`
,
Params
:
map
[
string
]
interface
{}{
"songinfo"
:
songInfo
},
}
iter
:=
client
.
Single
().
Query
(
ctx
,
stmt
)
defer
iter
.
Stop
()
for
{
row
,
err
:=
iter
.
Next
()
if
err
==
iterator
.
Done
{
return
nil
}
if
err
!=
nil
{
return
err
}
var
singerID
int64
var
songName
string
if
err
:=
row
.
Columns
(
& singerID
,
& songName
);
err
!=
nil
{
return
err
}
fmt
.
Fprintf
(
w
,
"%d %s\n"
,
singerID
,
songName
)
}
}
Java
static
void
queryNestedStructField
(
DatabaseClient
dbClient
)
{
Type
nameType
=
Type
.
struct
(
Arrays
.
asList
(
StructField
.
of
(
"FirstName"
,
Type
.
string
()),
StructField
.
of
(
"LastName"
,
Type
.
string
())));
Struct
songInfo
=
Struct
.
newBuilder
()
.
set
(
"song_name"
)
.
to
(
"Imagination"
)
.
set
(
"artistNames"
)
.
toStructArray
(
nameType
,
Arrays
.
asList
(
Struct
.
newBuilder
()
.
set
(
"FirstName"
)
.
to
(
"Elena"
)
.
set
(
"LastName"
)
.
to
(
"Campbell"
)
.
build
(),
Struct
.
newBuilder
()
.
set
(
"FirstName"
)
.
to
(
"Hannah"
)
.
set
(
"LastName"
)
.
to
(
"Harris"
)
.
build
()))
.
build
();
Statement
s
=
Statement
.
newBuilder
(
"SELECT SingerId, @song_info.song_name "
+
"FROM Singers WHERE "
+
"STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) "
+
"IN UNNEST(@song_info.artistNames)"
)
.
bind
(
"song_info"
)
.
to
(
songInfo
)
.
build
();
try
(
ResultSet
resultSet
=
dbClient
.
singleUse
().
executeQuery
(
s
))
{
while
(
resultSet
.
next
())
{
System
.
out
.
printf
(
"%d %s\n"
,
resultSet
.
getLong
(
"SingerId"
),
resultSet
.
getString
(
1
));
}
}
}
Node.js
// Imports the Google Cloud client library
const
{
Spanner
}
=
require
(
' @google-cloud/spanner
'
);
/**
* TODO(developer): Uncomment the following lines before running the sample.
*/
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// Creates a client
const
spanner
=
new
Spanner
({
projectId
:
projectId
,
});
// Gets a reference to a Cloud Spanner instance and database
const
instance
=
spanner
.
instance
(
instanceId
);
const
database
=
instance
.
database
(
databaseId
);
const
nameType
=
{
type
:
' struct
'
,
fields
:
[
{
name
:
'FirstName'
,
type
:
'string'
,
},
{
name
:
'LastName'
,
type
:
'string'
,
},
],
};
// Creates Song info STRUCT with a nested ArtistNames array
const
songInfoType
=
{
type
:
' struct
'
,
fields
:
[
{
name
:
'SongName'
,
type
:
'string'
,
},
{
name
:
'ArtistNames'
,
type
:
'array'
,
child
:
nameType
,
},
],
};
const
songInfoStruct
=
Spanner
.
struct
({
SongName
:
'Imagination'
,
ArtistNames
:
[
Spanner
.
struct
({
FirstName
:
'Elena'
,
LastName
:
'Campbell'
}),
Spanner
.
struct
({
FirstName
:
'Hannah'
,
LastName
:
'Harris'
}),
],
});
const
query
=
{
sql
:
'SELECT SingerId, @songInfo.SongName FROM Singers '
+
'WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) '
+
'IN UNNEST(@songInfo.ArtistNames)'
,
params
:
{
songInfo
:
songInfoStruct
,
},
types
:
{
songInfo
:
songInfoType
,
},
};
// Queries rows from the Singers table
try
{
const
[
rows
]
=
await
database
.
run
(
query
);
rows
.
forEach
(
row
=
>
{
const
json
=
row
.
toJSON
();
console
.
log
(
`SingerId:
${
json
.
SingerId
}
, SongName:
${
json
.
SongName
}
`
);
});
}
catch
(
err
)
{
console
.
error
(
'ERROR:'
,
err
);
}
finally
{
// Close the database when finished.
database
.
close
();
}
PHP
use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Database;
use Google\Cloud\Spanner\StructType;
use Google\Cloud\Spanner\StructValue;
use Google\Cloud\Spanner\ArrayType;
/**
* Queries sample data from the database using a nested struct field value.
* Example:
* ```
* query_data_with_nested_struct_field($instanceId, $databaseId);
* ```
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function query_data_with_nested_struct_field(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$nameType = new ArrayType(
(new StructType)
->add('FirstName', Database::TYPE_STRING)
->add('LastName', Database::TYPE_STRING)
);
$songInfoType = (new StructType)
->add('SongName', Database::TYPE_STRING)
->add('ArtistNames', $nameType);
$nameStructValue1 = (new StructValue)
->add('FirstName', 'Elena')
->add('LastName', 'Campbell');
$nameStructValue2 = (new StructValue)
->add('FirstName', 'Hannah')
->add('LastName', 'Harris');
$songInfoValues = (new StructValue)
->add('SongName', 'Imagination')
->add('ArtistNames', [$nameStructValue1, $nameStructValue2]);
$results = $database->execute(
'SELECT SingerId, @song_info.SongName FROM Singers ' .
'WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) ' .
'IN UNNEST(@song_info.ArtistNames)',
[
'parameters' => [
'song_info' => $songInfoValues
],
'types' => [
'song_info' => $songInfoType
]
]
);
foreach ($results as $row) {
printf('SingerId: %s SongName: %s' . PHP_EOL,
$row['SingerId'], $row['SongName']);
}
}
Python
def
query_nested_struct_field
(
instance_id
,
database_id
):
"""Query a table using nested field access on a STRUCT parameter."""
spanner_client
=
spanner
.
Client
()
instance
=
spanner_client
.
instance
(
instance_id
)
database
=
instance
.
database
(
database_id
)
song_info_type
=
param_types
.
Struct
(
[
param_types
.
StructField
(
"SongName"
,
param_types
.
STRING
),
param_types
.
StructField
(
"ArtistNames"
,
param_types
.
Array
(
param_types
.
Struct
(
[
param_types
.
StructField
(
"FirstName"
,
param_types
.
STRING
),
param_types
.
StructField
(
"LastName"
,
param_types
.
STRING
),
]
)
),
),
]
)
song_info
=
(
"Imagination"
,
[(
"Elena"
,
"Campbell"
),
(
"Hannah"
,
"Harris"
)])
with
database
.
snapshot
()
as
snapshot
:
results
=
snapshot
.
execute_sql
(
"SELECT SingerId, @song_info.SongName "
"FROM Singers WHERE "
"STRUCT<FirstName STRING, LastName STRING>"
"(FirstName, LastName) "
"IN UNNEST(@song_info.ArtistNames)"
,
params
=
{
"song_info"
:
song_info
},
param_types
=
{
"song_info"
:
song_info_type
},
)
for
row
in
results
:
print
(
"SingerId:
{}
SongName:
{}
"
.
format
(
*
row
))
Ruby
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require
"google/cloud/spanner"
spanner
=
Google
::
Cloud
::
Spanner
.
new
project
:
project_id
client
=
spanner
.
client
instance_id
,
database_id
name_type
=
client
.
fields
FirstName
:
:STRING
,
LastName
:
:STRING
song_info_struct
=
{
SongName
:
"Imagination"
,
ArtistNames
:
[
name_type
.
struct
(
[
"Elena"
,
"Campbell"
]
),
name_type
.
struct
(
[
"Hannah"
,
"Harris"
]
)
]
}
client
.
execute
(
"SELECT SingerId, @song_info.SongName "
\
"FROM Singers WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) "
\
"IN UNNEST(@song_info.ArtistNames)"
,
params
:
{
song_info
:
song_info_struct
}
)
.
rows
.
each
do
|
row
|
puts
(
row
[
:SingerId
]
),
(
row
[
:SongName
]
)
end
