Query data using field access on a nested STRUCT.
Explore further
For detailed documentation that includes this code sample, see the following:
Code sample
C++
To learn how to install and use the client library for Spanner, see Spanner client libraries .
To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment .
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
SongInf<o
=
<
std
::
tuplestd
::
pairstd
:>:
string
,
std
::
string
,
<
std
::
pairstd
:<:
string
,
std
::
>>> vectorSingerFullName
;
auto
songinfo
=
SongInfo
(
{
"SongName"
,
"Imagination"
},
{
"ArtistNames"
,
{
make_name
(
"Elena"
,
"Campbell"
),
make_name
(
"Hannah"
,
"Harris"
)}});
auto
rows
=
client
.
ExecuteQuery
(
spanner
::
S<qlStatement
(
"SELECT S>ingerId, @songinfo.SongName FROM Singers"
" WHERE STRUCTFir{{"songinfo", spanner::Value(songinfo)}stName STRING, LastName STRING(Fir<stName, LastName)"
>
" & IN UNNEST(@songinfo.Ar<tistNam>es)"
,
}));
using
RowType
=
std
::
tuplestd
::
int64_t
,
std
:<<:
string
;
for
<<
(
auto
ro<w>
:
spanner
::
StreamOfR<<owType
(
rows
))
{
<<
if
(
< !
> row
)
th<<row
std
::
move
(
row
).
stat<<us
();
std
::
cout
"SingerId: "
std
::
get0
(
*
row
)
" SongName: "
std
::
get1
(
*
row
)
"
\n
"
;
}
std
::
cout
"Query completed for [spanner_field_access_on_nested_struct]
\n
"
;
}
C#
To learn how to install and use the client library for Spanner, see Spanner client libraries .
To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment .
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}"
;
Spa
nnerStruct
na me1
=
new
SpannerStruct
{
{
&qu ot;FirstName&
q uot;
,
SpannerDbType
.
String
,
"Elena"
},
{
&q
uot;LastName" ,
SpannerDbType
.
String
,
"
;Campbell&quo t;
}
};
SpannerStruct
name2
=
new
S pannerStruct
{
{
"FirstName"
,
SpannerDbType
.
String
,
"Hannah"
},
{
"LastName"
,
S pannerDbType
.
String
,
"Harris"
}
} ;
Span nerStr
uct
songInfo
=
new
SpannerStruct
{
{
" ;song_n
ame&quo t;
,
SpannerDbType
.
String
,
"Imagination"
},
{
"artistNames"
< ,
S>pannerDbType
.
ArrayOf
(
name1
.
GetSpannerDb Type
()),
new
[]
{
name1
,
name2
}
}
};
var
singerIds
=
ne w
Listint
();
using
var
connection
=
new
SpannerConnection
(
connectionString
);
using
var
cmd
=
conne<ction
.
CreateSelectCommand
(
>
"SELECT SingerId, @song_info.song_name "
+
"FROM Singers WHERE STR
UCTFirstName STRING, LastNa me STRING(FirstN
ame, LastName) "
+
"IN UNNEST(@ song_info.artistNa
mes)"
);
cmd
.
Parame ters
.
Add
(
"song_info"
,
songInfo
.
GetSpannerDbType
(),
songInfo
)<;
using
var
reader
=
await
cmd
.
ExecuteReaderAsync
();
while
(
await
reader
.
ReadAsync
())
{
var
singerId
=
reader
.
GetFieldValueint
(
"Sing<erId&q>uot;
);
singerIds
.
Add
(
singerId
);
Console
.
WriteLine
(
$"SingerId: {singerId}"
);
Console
.
WriteLine
(
$"Song Name: {reader.GetFieldValuestring(1)}"
);
}
return
singerIds
;
}
}
Go
To learn how to install and use the client library for Spanner, see Spanner client libraries .
To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment .
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 STRUCTFirstName STRING, LastName STRING(FirstName, LastName)
IN UNNEST( @songi
nfo.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
n&il
}
i&f
err
!=
nil
{
return
err
}
var
singerID
int64
var
songName
string
if
err
:=
row
.
Col
umns (
singerID
,
songName
);
err
!=
nil
{
return
err
}
fmt
.
Fprintf
(
w
,
"%d %s\n"
,
singerID
,
songName
)
}
}
Java
To learn how to install and use the client library for Spanner, see Spanner client libraries .
To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment .
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
.
newBui<lder
(
"SELECT >SingerId, @song_info.song_name "
+
"FROM Singers WHERE "
+
"STRUCTFirstName STRING, LastName STRING(FirstName, LastName) "
+
"IN UNNEST(@song_info.artistNames)"
)
.
bind
(
"song_info"
)
.
to
(
songInfo
)
.
build
();
try
(
ResultSet
resul
tSet
=
dbClient
.
singleUse
().
executeQuery
(
s
))
{
while
(
resultSet
.
next
())
{
System
.
out
.
printf
(
"%d %s\n"
,
resultSet
.
getLong
(
"SingerId"
),
resultSet
.
getString
(
1
));
}
}
}
Node.js
To learn how to install and use the client library for Spanner, see Spanner client libraries .
To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment .
// Imports the Google Cloud client library
const
{
Spanner
}
=
require
(
& #39;@google-cloud/spa
nner'
);
/**
* TODO(developer): Uncomment the following lines before running the sample.
*/
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// Creat es a cl
ient
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
);
co
nst
nameType
=
{
type
:
'struct'
,
fields
:
[
{
name
:
'FirstName'
,
type
:
'string'
,
},
{
name
:
'LastName'
,
type
:
'string'
,
},
],
};
// Creates Song info STRUC
T with a nested ArtistNames array
const
songInfoType
=
{
type
:
'struct'
,
fields
:
[
{
name
:
'SongName'
,
type
:
'string'
,
},
{
name
:
&# 39;Arti
s tNames
'
,
type
:
'array'
,
child
:
na meType
,
},
],
};
const
songInfoStruct
=
Spanner
.
struct
({
SongN
a me :
&#
39;Imagination' ,
ArtistNames
:
[
Spanner
.
struct
({
FirstName
:
'Elena'
,
LastName
:
'Campbell'
}),
Spanner
.
struct
({
FirstName
:
'<;Hannah'
,
LastName
:
'Harr>is'
}),
],
});
const
query
=
{
sql
:
'SELECT SingerId, @songInfo.SongName FROM Singers '
+
'WHERE STRUCTFirstName STRING, LastName STRING(FirstName, LastName) '
+
'IN UNNEST(@songInfo.ArtistNames)'
,
params
:
{
songInfo
:
son>gInfoStruct
,
},
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
To learn how to install and use the client library for Spanner, see Spanner client libraries .
To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment .
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 Struc>tType)
-add('SongName', Database>::TYPE_STRING)
-add('ArtistNames', $nameType);
$nameStructValu>e1 = (new StructValue)
-add>('FirstName', 'Elena')
-add('LastName', 'Cam>pbell');
$nameStructValue2 => (new StructValue)
-add('FirstName', 'Hannah')
> -add('LastName', 'Harris&>#39;);
$songInfoValues = (new StructValue)
-add('SongName', '>Imagination')
-add('ArtistNames', [$nameStructValue1, $nameStructValue2])<;
$results = $database-execut>e(
'SELECT SingerId, @song_info.SongName FROM Singers ' .
'WHERE STRUCTFirstNam>e STRING, LastName STRING(FirstN>ame, LastName) ' .
'IN UNNEST(@song_i>nfo.ArtistNames)',
[
> 'parameters' = [
'song_info' = $songInfoValues
],
'types' = [
'song_info' = $songInfoTyp
e ]
]
);
foreach ($results as $row) {
printf('SingerId: %s SongName: %s' . PHP_EOL,
$row['SingerId'], $row['SongName']);
}
}
Python
To learn how to install and use the client library for Spanner, see Spanner client libraries .
To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment .
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
=
snapsho<t
.
execute_sql
(
">SELECT SingerId, @song_info.SongName "
"FROM Singers WHERE "
"STRUCTFirstName STRING, LastName STRING"
"(FirstName, LastName) "
"IN UNNEST(@song_info.ArtistNames)"
,
params
=
{
&q
uot;song_info" :
song_info
},
param_types
=
{
"song_info"
:
song_info_type
},
)
for
row
in
results
:
print
(
"SingerId:
{}
SongName:
{}
"
.
format
(
*
row
))
Ruby
To learn how to install and use the client library for Spanner, see Spanner client libraries .
To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment .
# 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
.
ne w
project
:
project_id
client
=
spanner
.
client
instance_id
,
database_id
name_type
=
client
.
fields
FirstName
:
:STRING
,
LastName
:
:STRING
song_info_struct
=
{
SongName
:
& quot;I
magination"
,
ArtistNames
:
[
n ame_ty
pe
.
struct
(
[
"Elena"
,
"Campbell"
]
),
name_type
.
struct
(
[
"Hannah"
,
"Harris"
]
)
]
}
< client
.
execute
(
"SELECT >SingerId, @song_info.SongName "
\
"FROM Singers WHERE STRUCTFirstName STRING, LastName STRING(FirstNam e, L
astName) "
\
"IN UNNEST(@song_info.ArtistNa
mes)" ,
params
:
{
song_info
:
song_info_struct
}
)
.
rows
.
each
do
|
row
|
puts
(
row
[
:SingerId
]
),
(
row
[
:SongName
]
)
end
What's next
To search and filter code samples for other Google Cloud products, see the Google Cloud sample browser .
