Skip to content

Bigquery: array-of-structs not working as named parameter in the java library #2485

@trias-at-alloy

Description

@trias-at-alloy

Environment details

  1. OS type and version: Mac OS
  2. Java version: 17
  3. version(s):
bq version:
This is BigQuery CLI 2.0.83

google-cloud-bigquery library: 2.18.2

Steps to reproduce

  1. execute the bq -command line code below
  2. execute the equivalent java code, which fails with Cannot convert STRUCT to String value

Code example

it works to supply a parameter as an array of structs:

bq query \
    --use_legacy_sql=false \          
    --parameter='tupels:ARRAY<STRUCT<a INT64, b INT64>>:[{"a":1, "b": 2}]' \
    'SELECT * FROM (SELECT 1 as a, 2 as b) WHERE (a, b) IN UNNEST(@tupels)'

works, while it does not seem to be possible to construct the same query with the java api:

  public static void queryWithArrayOfStructsNamedParams() {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      // Create struct and add it to the list
      Map<String, QueryParameterValue> structMap = new HashMap<>();
      structMap.put("a", QueryParameterValue.int64(1));
      structMap.put("b", QueryParameterValue.int64(2));

      QueryParameterValue structQueryParam = QueryParameterValue.struct(structMap);
      List<QueryParameterValue> tupels = new ArrayList<>();
      tupels.add(structQueryParam);

      String query = "SELECT * FROM (SELECT 1 as a, 2 as b) WHERE (a, b) IN UNNEST(@tupels)";
      QueryJobConfiguration queryConfig =
          QueryJobConfiguration.newBuilder(query)
              .setUseLegacySql(false)
              .addNamedParameter("tupels", QueryParameterValue.array(tupels.toArray(), StandardSQLTypeName.STRUCT))
              .build();
      TableResult results = bigquery.query(queryConfig);
      results
          .iterateAll()
          .forEach(row -> row.forEach(val -> logger.info(val.toString())));
      System.out.println("Query with Array of struct parameter performed successfully.");
    } catch (Exception e) {
      System.out.println("Query not performed \n" + e);
    }
  }

Stack trace

the above code gives this stack trace

java.lang.IllegalArgumentException: Cannot convert STRUCT to String value
	at com.google.cloud.bigquery.QueryParameterValue.valueToStringOrNull(QueryParameterValue.java:440)
	at com.google.cloud.bigquery.QueryParameterValue.of(QueryParameterValue.java:216)
	at com.google.cloud.bigquery.QueryParameterValue.array(QueryParameterValue.java:352)
// ... custom code

External references such as API reference guides

Metadata

Metadata

Assignees

No one assigned

    Labels

    api: bigqueryIssues related to the googleapis/java-bigquery API.type: bugError or flaw in code with unintended results or allowing sub-optimal usage patterns.

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions