fix: add support for repeated record query parameters

[obada-ab]

Add support for array of struct query parameters which are used to query repeated record fields

Fixes #2485 ☕️

[Neenu1995]

Where is this restriction coming from?

[obada-ab]

There is currently no way for users to supply detailed struct types (including the fields) when creating an array of struct query parameter using the QueryParameterValue.array method, which would limit us to the option of peeking at the first record to figure out the struct fields.

Ideally, query parameter types should not be limited to primitives and StandardSQLTypeName, users should create more general/nested types and supply them to QueryParameterValue.array, but this would be a much bigger change.

I think this fix with the limitation would solve most/all use cases until the changes mentioned are made.

[Neenu1995]

Can you also write a test with UNNEST option, since that is the particular use case the customer is using?

[obada-ab]

Sure, will work on it

[obada-ab]

@Neenu1995 I've made some changes:

• The toTypePb method now only peeks at struct array values if they exist, otherwise it defaults to generating an empty struct array type
• Added two additional integration tests:
  • testUnnestRepeatedRecordNamedQueryParameter: tests UNNESTing a struct array query parameter
  • testEmptyRepeatedRecordNamedQueryParameters: tests creating an empty struct array query parameter. Creating the query parameter itself works but executing IN UNNEST for said parameter should result in an exception

Also there's something to note: the toTypePb is only used for query parameters, returned query results only go through toValuePb which wasn't touched in this PR.

[Neenu1995]

String query = "SELECT string, arrayOfStruct FROM `datasetname.tablename`, "
          + "UNNEST(arrayOfStruct) as TEMP WHERE TEMP IN UNNEST(@states);";

      // states - Parameterized query type of Array of Struct
      // arrayOfStruct - Field of type Array of Struct

This is the exact statement we want to verify. Verify if a named parameter type of Array of struct exists in a struct field.
Please create a table and try querying from there.

[obada-ab]

The order of fields inside structs matters when doing comparisons, and HashMap does not maintain insertion order. Because of these reasons, the IN UNNEST(addresses) query doesn't return any results.

I tried replacing HashMap with a LinkedHashMap and using ImmutableMap.of, both options made the IN UNNEST(addresses) query work as intended and return the correct row.

[snippet-bot]

Here is the summary of changes.

You are about to add 1 region tag.

• samples/snippets/src/main/java/com/example/bigquery/CreateAndQueryRepeatedRecordField.java:19, tag bigquery_create_and_query_repeated_record

---

This comment is generated by snippet-bot.
If you find problems with this result, please file an issue at:
https://github.com/googleapis/repo-automation-bots/issues.
To update this comment, add snippet-bot:force-run label or use the checkbox below:

• Refresh this comment