Repository: snowflakedb/gosnowflake
Pull request #1087 (2024-03-29 11:03:38)

SNOW-1256916 implement structured arrays
### Description

SNOW-1256916 Implemented structured arrays

### Checklist
- [ ] Created tests which fail without the change (if possible)
- [ ] Extended the README / documentation, if necessary



Patch:
diff --git a/async.go b/async.go
index b6fb227..95c0862 100644
--- a/async.go
+++ b/async.go
@@ -30,6 +30,7 @@ func (sr *snowflakeRestful) processAsync(
 		rows.queryID = respd.Data.QueryID
 		rows.status = QueryStatusInProgress
 		rows.errChannel = make(chan error)
+		rows.ctx = ctx
 		respd.Data.AsyncRows = rows
 	default:
 		return respd, nil
diff --git a/connection.go b/connection.go
index 2cb92b6..01d8aba 100644
--- a/connection.go
+++ b/connection.go
@@ -420,6 +420,7 @@ func (sc *snowflakeConn) queryContextInternal(
 	rows := new(snowflakeRows)
 	rows.sc = sc
 	rows.queryID = data.Data.QueryID
+	rows.ctx = ctx
 
 	if isMultiStmt(&data.Data) {
 		// handleMultiQuery is responsible to fill rows with childResults
diff --git a/converter.go b/converter.go
index cd292a8..86d3ac4 100644
--- a/converter.go
+++ b/converter.go
@@ -9,6 +9,7 @@ import (
 	"database/sql/driver"
 	"encoding/hex"
 	"encoding/json"
+	"errors"
 	"fmt"
 	"math"
 	"math/big"
@@ -109,7 +110,7 @@ func goTypeToSnowflake(v driver.Value, tsmode snowflakeType) snowflakeType {
 }
 
 // snowflakeTypeToGo translates Snowflake data type to Go data type.
-func snowflakeTypeToGo(dbtype snowflakeType, scale int64, fields []fieldMetadata) reflect.Type {
+func snowflakeTypeToGo(ctx context.Context, dbtype snowflakeType, scale int64, fields []fieldMetadata) reflect.Type {
 	switch dbtype {
 	case fixedType:
 		if scale == 0 {
@@ -118,7 +119,7 @@ func snowflakeTypeToGo(dbtype snowflakeType, scale int64, fields []fieldMetadata
 		return reflect.TypeOf(float64(0))
 	case realType:
 		return reflect.TypeOf(float64(0))
-	case textType, variantType, arrayType:
+	case textType, variantType:
 		return reflect.TypeOf("")
 	case dateType, timeType, timestampLtzType, timestampNtzType, timestampTzType:
 		return reflect.TypeOf(time.Now())
@@ -131,6 +132,38 @@ func snowflakeTypeToGo(dbtype snowflakeType, scale int64, fields []fieldMetadata
 			return reflect.TypeOf(ObjectType{})
 		}
 		return reflect.TypeOf("")
+	case arrayType:
+		if len(fields) == 0 {
+			return reflect.TypeOf("")
+		}
+		if len(fields) != 1 {
+			logger.Warn("Unexpected fields number: " + strconv.Itoa(len(fields)))
+			return reflect.TypeOf("")
+		}
+		switch getSnowflakeType(fields[0].Type) {
+		case fixedType:
+			if fields[0].Scale == 0 && higherPrecisionEnabled(ctx) {
+				return reflect.TypeOf([]*big.Int{})
+			} else if fields[0].Scale == 0 && !higherPrecisionEnabled(ctx) {
+				return reflect.TypeOf([]int64{})
+			} else if fields[0].Scale != 0 && higherPrecisionEnabled(ctx) {
+				return reflect.TypeOf([]*big.Float{})
+			}
+			return reflect.TypeOf([]float64{})
+		case realType:
+			return reflect.TypeOf([]float64{})
+		case textType:
+			return reflect.TypeOf([]string{})
+		case dateType, timeType, timestampLtzType, timestampNtzType, timestampTzType:
+			return reflect.TypeOf([]time.Time{})
+		case booleanType:
+			return reflect.TypeOf([]bool{})
+		case binaryType:
+			return reflect.TypeOf([][]byte{})
+		case objectType:
+			return reflect.TypeOf([]ObjectType{})
+		}
+		return nil
 	}
 	logger.Errorf("unsupported dbtype is specified. %v", dbtype)
 	return reflect.TypeOf("")
@@ -361,11 +394,112 @@ func stringToValue(dest *driver.Value, srcColumnMeta execResponseRowType, srcVal
 		}
 		*dest = b
 		return nil
+	case "array":
+		if len(srcColumnMeta.Fields) == 0 {
+			*dest = *srcValue
+			return nil
+		}
+		if len(srcColumnMeta.Fields) > 1 {
+			return errors.New("got more than one field for array")
+		}
+		v, err := buildStructuredArray(srcColumnMeta.Fields[0], srcValue, params)
+		if err != nil {
+			return err
+		}
+		*dest = v
+		return nil
 	}
 	*dest = *srcValue
 	return nil
 }
 
+func buildStructuredArray(fieldMetadata fieldMetadata, srcValue *string, params map[string]*string) (any, error) {
+	switch fieldMetadata.Type {
+	case "text":
+		var v []string
+		err := json.Unmarshal([]byte(*srcValue), &v)
+		if err != nil {
+			return nil, err
+		}
+		return v, nil
+	case "fixed":
+		if fieldMetadata.Scale == 0 {
+			var v []int64
+			err := json.Unmarshal([]byte(*srcValue), &v)
+			if err != nil {
+				return nil, err
+			}
+			return v, nil
+		}
+		var v []float64
+		err := json.Unmarshal([]byte(*srcValue), &v)
+		if err != nil {
+			return nil, err
+		}
+		return v, nil
+	case "real":
+		var v []float64
+		err := json.Unmarshal([]byte(*srcValue), &v)
+		if err != nil {
+			return nil, err
+		}
+		return v, nil
+	case "time", "date", "timestamp_ltz", "timestamp_ntz", "timestamp_tz":
+		var strings []string
+		err := json.Unmarshal([]byte(*srcValue), &strings)
+		if err != nil {
+			return nil, err
+		}
+		v := make([]time.Time, len(strings))
+		sfFormat, err := dateTimeFormatByType(fieldMetadata.Type, params)
+		if err != nil {
+			return nil, err
+		}
+		goFormat, err := snowflakeFormatToGoFormat(sfFormat)
+		if err != nil {
+			return nil, err
+		}
+		for i, s := range strings {
+			if v[i], err = time.Parse(goFormat, s); err != nil {
+				return nil, err
+			}
+		}
+		return v, nil
+	case "boolean":
+		var v []bool
+		err := json.Unmarshal([]byte(*srcValue), &v)
+		if err != nil {
+			return nil, err
+		}
+		return v, nil
+	case "binary":
+		var strings []string
+		err := json.Unmarshal([]byte(*srcValue), &strings)
+		if err != nil {
+			return nil, err
+		}
+		v := make([][]byte, len(strings))
+		for i, s := range strings {
+			if v[i], err = hex.DecodeString(s); err != nil {
+				return nil, err
+			}
+		}
+		return v, nil
+	case "object":
+		var maps []map[string]any
+		err := json.Unmarshal([]byte(*srcValue), &maps)
+		if err != nil {
+			return nil, err
+		}
+		objects := make([]*structuredType, len(maps))
+		for i, m := range maps {
+			objects[i] = buildStructuredTypeRecursive(m, fieldMetadata.Fields, params)
+		}
+		return objects, nil
+	}
+	return srcValue, nil
+}
+
 func buildStructuredTypeRecursive(m map[string]any, fields []fieldMetadata, params map[string]*string) *structuredType {
 	for _, fm := range fields {
 		if fm.Type == "object" {
@@ -521,12 +655,114 @@ func arrowToValue(rowIdx int, srcColumnMeta execResponseRowType, srcValue arrow.
 		// Snowflake data types that are floating-point numbers will fall in this category
 		// e.g. FLOAT/REAL/DOUBLE
 		return arrowRealToValue(srcValue.(*array.Float64), rowIdx), nil
-	case textType, arrayType, variantType:
+	case textType, variantType:
 		strings := srcValue.(*array.String)
 		if !srcValue.IsNull(rowIdx) {
 			return strings.Value(rowIdx), nil
 		}
 		return nil, nil
+	case arrayType:
+		if len(srcColumnMeta.Fields) == 0 {
+			// semistructured type without schema
+			strings := srcValue.(*array.String)
+			if !srcValue.IsNull(rowIdx) {
+				return strings.Value(rowIdx), nil
+			}
+			return nil, nil
+		}
+		strings, ok := srcValue.(*array.String)
+		if ok {
+			// structured array as json
+			if !srcValue.IsNull(rowIdx) {
+				val := strings.Value(rowIdx)
+				return buildStructuredArray(srcColumnMeta.Fields[0], &val, params)
+			}
+			return nil, nil
+		}
+		list := srcValue.(*array.List)
+		if list.IsNull(rowIdx) {
+			return nil, nil
+		}
+		values := list.ListValues()
+		offsets := list.Offsets()
+		snowflakeType := getSnowflakeType(srcColumnMeta.Fields[0].Type)
+		switch snowflakeType {
+		case fixedType:
+			switch values.(type) {
+			case *array.Decimal128:
+				if higherPrecision && srcColumnMeta.Fields[0].Scale == 0 {
+					return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (*big.Int, error) {
+						return arrowDecimal128ToValue(values.(*array.Decimal128), j, higherPrecision, srcColumnMeta.Fields[0].Scale).(*big.Int), nil
+					})
+				} else if higherPrecision && srcColumnMeta.Fields[0].Scale != 0 {
+					return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (*big.Float, error) {
+						return arrowDecimal128ToValue(values.(*array.Decimal128), j, higherPrecision, srcColumnMeta.Fields[0].Scale).(*big.Float), nil
+					})
+				} else if !higherPrecision && srcColumnMeta.Fields[0].Scale == 0 {
+					return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (int64, error) {
+						v := arrowDecimal128ToValue(values.(*array.Decimal128), int(j), higherPrecision, srcColumnMeta.Fields[0].Scale)
+						return strconv.ParseInt(v.(string), 10, 64)
+					})
+				} else {
+					return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (float64, error) {
+						v := arrowDecimal128ToValue(values.(*array.Decimal128), int(j), higherPrecision, srcColumnMeta.Fields[0].Scale)
+						return strconv.ParseFloat(v.(string), 64)
+					})
+				}
+			case *array.Int64:
+				return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (int64, error) {
+					return arrowInt64ToValue(values.(*array.Int64), int(j), higherPrecision, srcColumnMeta.Fields[0].Scale).(int64), nil
+				})
+			case *array.Int32:
+				return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (int32, error) {
+					return arrowInt32ToValue(values.(*array.Int32), int(j), higherPrecision, srcColumnMeta.Fields[0].Scale).(int32), nil
+				})
+			case *array.Int16:
+				return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (int16, error) {
+					return arrowInt16ToValue(values.(*array.Int16), int(j), higherPrecision, srcColumnMeta.Fields[0].Scale).(int16), nil
+				})
+			case *array.Int8:
+				return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (int8, error) {
+					return arrowInt8ToValue(values.(*array.Int8), int(j), higherPrecision, srcColumnMeta.Fields[0].Scale).(int8), nil
+				})
+			}
+		case realType:
+			return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (float64, error) {
+				return arrowRealToValue(values.(*array.Float64), j).(float64), nil
+			})
+		case textType:
+			return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (string, error) {
+				return values.(*array.String).Value(j), nil
+			})
+		case booleanType:
+			return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (bool, error) {
+				return values.(*array.Boolean).Value(j), nil
+			})
+		case binaryType:
+			return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) ([]byte, error) {
+				return values.(*array.Binary).Value(int(j)), nil
+			})
+		case dateType:
+			return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (time.Time, error) {
+				return arrowDateToValue(values.(*array.Date32), int(j)).(time.Time), nil
+			})
+		case timeType:
+			return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (time.Time, error) {
+				return arrowTimeToValue(values, int(j), srcColumnMeta.Fields[0].Scale).(time.Time), nil
+			})
+		case timestampNtzType, timestampLtzType, timestampTzType:
+			return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (time.Time, error) {
+				return *arrowSnowflakeTimestampToTime(values, snowflakeType, srcColumnMeta.Fields[0].Scale, int(j), loc), nil
+			})
+		case objectType:
+			return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (*structuredType, error) {
+				m := make(map[string]any, len(srcColumnMeta.Fields[0].Fields))
+				for fieldIdx, field := range srcColumnMeta.Fields[0].Fields {
+					m[field.Name] = values.(*array.Struct).Field(fieldIdx).ValueStr(int(j))
+				}
+				return buildStructuredTypeRecursive(m, srcColumnMeta.Fields[0].Fields, params), nil
+			})
+		}
 	case objectType:
 		if len(srcColumnMeta.Fields) == 0 {
 			// semistructured type without schema
@@ -570,6 +806,18 @@ func arrowToValue(rowIdx int, srcColumnMeta execResponseRowType, srcValue arrow.
 	return nil, fmt.Errorf("unsupported data type")
 }
 
+func mapStructuredArrayNativeArrowRows[T any](offsets []int32, rowIdx int, createValueFunc func(j int) (T, error)) (snowflakeValue, error) {
+	arr := make([]T, offsets[rowIdx+1]-offsets[rowIdx])
+	for j := offsets[rowIdx]; j < offsets[rowIdx+1]; j++ {
+		v, err := createValueFunc(int(j))
+		if err != nil {
+			return nil, err
+		}
+		arr[j-offsets[rowIdx]] = v
+	}
+	return arr, nil
+}
+
 func arrowToStructuredType(structs *array.Struct, fieldMetadata []fieldMetadata, loc *time.Location, rowIdx int, higherPrecision bool, params map[string]*string) *structuredType {
 	m := make(map[string]any)
 	for colIdx := 0; colIdx < structs.NumField(); colIdx++ {
diff --git a/monitoring.go b/monitoring.go
index 7da073f..19af715 100644
--- a/monitoring.go
+++ b/monitoring.go
@@ -258,6 +258,7 @@ func (sc *snowflakeConn) buildRowsForRunningQuery(
 	rows := new(snowflakeRows)
 	rows.sc = sc
 	rows.queryID = qid
+	rows.ctx = ctx
 	if err := sc.rowsForRunningQuery(ctx, qid, rows); err != nil {
 		return nil, err
 	}
diff --git a/rows.go b/rows.go
index 95a0ea8..8898c56 100644
--- a/rows.go
+++ b/rows.go
@@ -3,6 +3,7 @@
 package gosnowflake
 
 import (
+	"context"
 	"database/sql/driver"
 	"io"
 	"reflect"
@@ -44,6 +45,7 @@ type snowflakeRows struct {
 	err                 error
 	errChannel          chan error
 	location            *time.Location
+	ctx                 context.Context
 }
 
 func (rows *snowflakeRows) getLocation() *time.Location {
@@ -147,10 +149,7 @@ func (rows *snowflakeRows) ColumnTypeScanType(index int) reflect.Type {
 	if err := rows.waitForAsyncQueryStatus(); err != nil {
 		return nil
 	}
-	return snowflakeTypeToGo(
-		getSnowflakeType(rows.ChunkDownloader.getRowType()[index].Type),
-		rows.ChunkDownloader.getRowType()[index].Scale,
-		rows.ChunkDownloader.getRowType()[index].Fields)
+	return snowflakeTypeToGo(rows.ctx, getSnowflakeType(rows.ChunkDownloader.getRowType()[index].Type), rows.ChunkDownloader.getRowType()[index].Scale, rows.ChunkDownloader.getRowType()[index].Fields)
 }
 
 func (rows *snowflakeRows) GetQueryID() string {
diff --git a/structured_type.go b/structured_type.go
index ba437f3..4c4cd44 100644
--- a/structured_type.go
+++ b/structured_type.go
@@ -7,6 +7,7 @@ import (
 	"errors"
 	"fmt"
 	"math/big"
+	"reflect"
 	"strconv"
 	"time"
 )
@@ -32,6 +33,30 @@ type StructuredType interface {
 	GetStruct(fieldName string, scanner sql.Scanner) (sql.Scanner, error)
 }
 
+// ArrayOfScanners Helper type for scanning array of sql.Scanner values.
+type ArrayOfScanners[T sql.Scanner] []T
+
+func (st *ArrayOfScanners[T]) Scan(val any) error {
+	sts := val.([]*structuredType)
+	*st = make([]T, len(sts))
+	var t T
+	for i, s := range sts {
+		(*st)[i] = reflect.New(reflect.TypeOf(t).Elem()).Interface().(T)
+		if err := (*st)[i].Scan(s); err != nil {
+			return err
+		}
+	}
+	return nil
+}
+
+// ScanArrayOfScanners is a helper function for scanning arrays of sql.Scanner values.
+// Example:
+// var res []*simpleObject
+// err := rows.Scan(ScanArrayOfScanners(&res))
+func ScanArrayOfScanners[T sql.Scanner](value *[]T) *ArrayOfScanners[T] {
+	return (*ArrayOfScanners[T])(value)
+}
+
 type structuredType struct {
 	values        map[string]any
 	fieldMetadata []fieldMetadata


Test patch:
diff --git a/assert_test.go b/assert_test.go
index f24fa8d..40dc449 100644
--- a/assert_test.go
+++ b/assert_test.go
@@ -35,6 +35,10 @@ func assertEqualF(t *testing.T, actual any, expected any, descriptions ...string
 	fatalOnNonEmpty(t, validateEqual(actual, expected, descriptions...))
 }
 
+func assertDeepEqualE(t *testing.T, actual any, expected any, descriptions ...string) {
+	errorOnNonEmpty(t, validateDeepEqual(actual, expected, descriptions...))
+}
+
 func assertNotEqualF(t *testing.T, actual any, expected any, descriptions ...string) {
 	fatalOnNonEmpty(t, validateNotEqual(actual, expected, descriptions...))
 }
@@ -119,6 +123,14 @@ func validateEqual(actual any, expected any, descriptions ...string) string {
 	return fmt.Sprintf("expected \"%s\" to be equal to \"%s\" but was not. %s", actual, expected, desc)
 }
 
+func validateDeepEqual(actual any, expected any, descriptions ...string) string {
+	if reflect.DeepEqual(actual, expected) {
+		return ""
+	}
+	desc := joinDescriptions(descriptions...)
+	return fmt.Sprintf("expected \"%s\" to be equal to \"%s\" but was not. %s", actual, expected, desc)
+}
+
 func validateNotEqual(actual any, expected any, descriptions ...string) string {
 	if expected != actual {
 		return ""
diff --git a/converter_test.go b/converter_test.go
index 0b152b5..d973281 100644
--- a/converter_test.go
+++ b/converter_test.go
@@ -138,35 +138,48 @@ func TestGoTypeToSnowflake(t *testing.T) {
 	}
 }
 
-type tcSnowflakeTypeToGo struct {
-	in     snowflakeType
-	scale  int64
-	out    reflect.Type
-	fields []fieldMetadata
-}
-
 func TestSnowflakeTypeToGo(t *testing.T) {
-	testcases := []tcSnowflakeTypeToGo{
-		{in: fixedType, scale: 0, out: reflect.TypeOf(int64(0))},
-		{in: fixedType, scale: 2, out: reflect.TypeOf(float64(0))},
-		{in: realType, scale: 0, out: reflect.TypeOf(float64(0))},
-		{in: textType, scale: 0, out: reflect.TypeOf("")},
-		{in: dateType, scale: 0, out: reflect.TypeOf(time.Now())},
-		{in: timeType, scale: 0, out: reflect.TypeOf(time.Now())},
-		{in: timestampLtzType, scale: 0, out: reflect.TypeOf(time.Now())},
-		{in: timestampNtzType, scale: 0, out: reflect.TypeOf(time.Now())},
-		{in: timestampTzType, scale: 0, out: reflect.TypeOf(time.Now())},
-		{in: objectType, scale: 0, out: reflect.TypeOf("")},
-		{in: objectType, scale: 0, fields: []fieldMetadata{{}}, out: reflect.TypeOf(ObjectType{})},
-		{in: variantType, scale: 0, out: reflect.TypeOf("")},
-		{in: arrayType, scale: 0, out: reflect.TypeOf("")},
-		{in: binaryType, scale: 0, out: reflect.TypeOf([]byte{})},
-		{in: booleanType, scale: 0, out: reflect.TypeOf(true)},
-		{in: sliceType, scale: 0, out: reflect.TypeOf("")},
+	testcases := []struct {
+		in     snowflakeType
+		scale  int64
+		fields []fieldMetadata
+		out    reflect.Type
+		ctx    context.Context
+	}{
+		{in: fixedType, scale: 0, out: reflect.TypeOf(int64(0)), ctx: context.Background()},
+		{in: fixedType, scale: 2, out: reflect.TypeOf(float64(0)), ctx: context.Background()},
+		{in: realType, scale: 0, out: reflect.TypeOf(float64(0)), ctx: context.Background()},
+		{in: textType, scale: 0, out: reflect.TypeOf(""), ctx: context.Background()},
+		{in: dateType, scale: 0, out: reflect.TypeOf(time.Now()), ctx: context.Background()},
+		{in: timeType, scale: 0, out: reflect.TypeOf(time.Now()), ctx: context.Background()},
+		{in: timestampLtzType, scale: 0, out: reflect.TypeOf(time.Now()), ctx: context.Background()},
+		{in: timestampNtzType, scale: 0, out: reflect.TypeOf(time.Now()), ctx: context.Background()},
+		{in: timestampTzType, scale: 0, out: reflect.TypeOf(time.Now()), ctx: context.Background()},
+		{in: objectType, scale: 0, out: reflect.TypeOf(""), ctx: context.Background()},
+		{in: objectType, scale: 0, fields: []fieldMetadata{{}}, out: reflect.TypeOf(ObjectType{}), ctx: context.Background()},
+		{in: variantType, scale: 0, out: reflect.TypeOf(""), ctx: context.Background()},
+		{in: arrayType, scale: 0, out: reflect.TypeOf(""), ctx: context.Background()},
+		{in: binaryType, scale: 0, out: reflect.TypeOf([]byte{}), ctx: context.Background()},
+		{in: booleanType, scale: 0, out: reflect.TypeOf(true), ctx: context.Background()},
+		{in: sliceType, scale: 0, out: reflect.TypeOf(""), ctx: context.Background()},
+		{in: arrayType, scale: 0, fields: []fieldMetadata{{Type: "fixed", Scale: 0}}, out: reflect.TypeOf([]int64{}), ctx: context.Background()},
+		{in: arrayType, scale: 0, fields: []fieldMetadata{{Type: "fixed", Scale: 1}}, out: reflect.TypeOf([]float64{}), ctx: context.Background()},
+		{in: arrayType, scale: 0, fields: []fieldMetadata{{Type: "fixed", Scale: 0}}, out: reflect.TypeOf([]*big.Int{}), ctx: WithHigherPrecision(context.Background())},
+		{in: arrayType, scale: 0, fields: []fieldMetadata{{Type: "fixed", Scale: 1}}, out: reflect.TypeOf([]*big.Float{}), ctx: WithHigherPrecision(context.Background())},
+		{in: arrayType, scale: 0, fields: []fieldMetadata{{Type: "real", Scale: 1}}, out: reflect.TypeOf([]float64{}), ctx: context.Background()},
+		{in: arrayType, scale: 0, fields: []fieldMetadata{{Type: "text"}}, out: reflect.TypeOf([]string{}), ctx: context.Background()},
+		{in: arrayType, scale: 0, fields: []fieldMetadata{{Type: "date"}}, out: reflect.TypeOf([]time.Time{}), ctx: context.Background()},
+		{in: arrayType, scale: 0, fields: []fieldMetadata{{Type: "time"}}, out: reflect.TypeOf([]time.Time{}), ctx: context.Background()},
+		{in: arrayType, scale: 0, fields: []fieldMetadata{{Type: "timestamp_ntz"}}, out: reflect.TypeOf([]time.Time{}), ctx: context.Background()},
+		{in: arrayType, scale: 0, fields: []fieldMetadata{{Type: "timestamp_ltz"}}, out: reflect.TypeOf([]time.Time{}), ctx: context.Background()},
+		{in: arrayType, scale: 0, fields: []fieldMetadata{{Type: "timestamp_tz"}}, out: reflect.TypeOf([]time.Time{}), ctx: context.Background()},
+		{in: arrayType, scale: 0, fields: []fieldMetadata{{Type: "boolean"}}, out: reflect.TypeOf([]bool{}), ctx: context.Background()},
+		{in: arrayType, scale: 0, fields: []fieldMetadata{{Type: "binary"}}, out: reflect.TypeOf([][]byte{}), ctx: context.Background()},
+		{in: arrayType, scale: 0, fields: []fieldMetadata{{Type: "object"}}, out: reflect.TypeOf([]ObjectType{}), ctx: context.Background()},
 	}
 	for _, test := range testcases {
 		t.Run(fmt.Sprintf("%v_%v", test.in, test.out), func(t *testing.T) {
-			a := snowflakeTypeToGo(test.in, test.scale, test.fields)
+			a := snowflakeTypeToGo(test.ctx, test.in, test.scale, test.fields)
 			if a != test.out {
 				t.Errorf("failed. in: %v, scale: %v, expected: %v, got: %v",
 					test.in, test.scale, test.out, a)
diff --git a/structured_type_test.go b/structured_type_test.go
index c02b4b8..9bd2214 100644
--- a/structured_type_test.go
+++ b/structured_type_test.go
@@ -81,6 +81,7 @@ func (o *objectWithAllTypes) Scan(val any) error {
 
 type simpleObject struct {
 	s string
+	i int
 }
 
 func (so *simpleObject) Scan(val any) error {
@@ -89,6 +90,9 @@ func (so *simpleObject) Scan(val any) error {
 	if so.s, err = st.GetString("s"); err != nil {
 		return err
 	}
+	if so.i, err = st.GetInt("i"); err != nil {
+		return err
+	}
 	return nil
 }
 
@@ -98,14 +102,14 @@ func skipStructuredTypesTestsOnGHActions(t *testing.T) {
 	}
 }
 
-func TestStructuredTypeWithAllTypes(t *testing.T) {
+func TestObjectWithAllTypes(t *testing.T) {
 	skipStructuredTypesTestsOnGHActions(t)
 	warsawTz, err := time.LoadLocation("Europe/Warsaw")
 	assertNilF(t, err)
 	runDBTest(t, func(dbt *DBTest) {
 		forAllStructureTypeFormats(dbt, func(t *testing.T, format string) {
 			dbt.mustExec("ALTER SESSION SET TIMEZONE = 'Europe/Warsaw'")
-			rows := dbt.mustQuery("SELECT 1, {'s': 'some string', 'b': 1, 'i16': 2, 'i': 3, 'i64': 9223372036854775807, 'f32': '1.1', 'f64': 2.2, 'bo': true, 'bi': TO_BINARY('616263', 'HEX'), 'date': '2024-03-21'::DATE, 'time': '13:03:02'::TIME, 'ltz': '2021-07-21 11:22:33'::TIMESTAMP_LTZ, 'tz': '2022-08-31 13:43:22 +0200'::TIMESTAMP_TZ, 'ntz': '2023-05-22 01:17:19'::TIMESTAMP_NTZ, 'so': {'s': 'child'}}::OBJECT(s VARCHAR, b TINYINT, i16 SMALLINT, i INTEGER, i64 BIGINT, f32 FLOAT, f64 DOUBLE, bo BOOLEAN, bi BINARY, date DATE, time TIME, ltz TIMESTAMP_LTZ, tz TIMESTAMP_TZ, ntz TIMESTAMP_NTZ, so OBJECT(s VARCHAR))")
+			rows := dbt.mustQuery("SELECT 1, {'s': 'some string', 'b': 1, 'i16': 2, 'i': 3, 'i64': 9223372036854775807, 'f32': '1.1', 'f64': 2.2, 'bo': true, 'bi': TO_BINARY('616263', 'HEX'), 'date': '2024-03-21'::DATE, 'time': '13:03:02'::TIME, 'ltz': '2021-07-21 11:22:33'::TIMESTAMP_LTZ, 'tz': '2022-08-31 13:43:22 +0200'::TIMESTAMP_TZ, 'ntz': '2023-05-22 01:17:19'::TIMESTAMP_NTZ, 'so': {'s': 'child', 'i': 9}}::OBJECT(s VARCHAR, b TINYINT, i16 SMALLINT, i INTEGER, i64 BIGINT, f32 FLOAT, f64 DOUBLE, bo BOOLEAN, bi BINARY, date DATE, time TIME, ltz TIMESTAMP_LTZ, tz TIMESTAMP_TZ, ntz TIMESTAMP_NTZ, so OBJECT(s VARCHAR, i INTEGER))")
 			defer rows.Close()
 			rows.Next()
 			var ignore int
@@ -128,7 +132,7 @@ func TestStructuredTypeWithAllTypes(t *testing.T) {
 			assertTrueE(t, res.ltz.Equal(time.Date(2021, time.July, 21, 11, 22, 33, 0, warsawTz)))
 			assertTrueE(t, res.tz.Equal(time.Date(2022, time.August, 31, 13, 43, 22, 0, warsawTz)))
 			assertTrueE(t, res.ntz.Equal(time.Date(2023, time.May, 22, 1, 17, 19, 0, time.UTC)))
-			assertEqualE(t, res.so, simpleObject{s: "child"})
+			assertEqualE(t, res.so, simpleObject{s: "child", i: 9})
 		})
 	})
 }
@@ -150,30 +154,6 @@ func (hps *HigherPrecisionStruct) Scan(val any) error {
 	return nil
 }
 
-func TestWithHigherPrecision(t *testing.T) {
-	skipStructuredTypesTestsOnGHActions(t)
-	runDBTest(t, func(dbt *DBTest) {
-		forAllStructureTypeFormats(dbt, func(t *testing.T, format string) {
-			if format != "NATIVE_ARROW" {
-				t.Skip("JSON structured type does not support higher precision")
-			}
-			ctx := WithHigherPrecision(context.Background())
-			rows := dbt.mustQueryContext(ctx, "SELECT {'i': 10000000000000000000000000000000000000::DECIMAL(38, 0), 'f': 1.2345678901234567890123456789012345678::DECIMAL(38, 37)}::OBJECT(i DECIMAL(38, 0), f DECIMAL(38, 37))")
-			defer rows.Close()
-			rows.Next()
-			var v HigherPrecisionStruct
-			err := rows.Scan(&v)
-			assertNilF(t, err)
-			bigInt, b := new(big.Int).SetString("10000000000000000000000000000000000000", 10)
-			assertTrueF(t, b)
-			assertEqualE(t, bigInt.Cmp(v.i), 0)
-			bigFloat, b := new(big.Float).SetPrec(v.f.Prec()).SetString("1.2345678901234567890123456789012345678")
-			assertTrueE(t, b)
-			assertEqualE(t, bigFloat.Cmp(v.f), 0)
-		})
-	})
-}
-
 func TestObjectMetadata(t *testing.T) {
 	skipStructuredTypesTestsOnGHActions(t)
 	runDBTest(t, func(dbt *DBTest) {
@@ -227,6 +207,290 @@ func TestObjectWithoutSchemaMetadata(t *testing.T) {
 	})
 }
 
+func TestArrayAndMetadata(t *testing.T) {
+	skipStructuredTypesTestsOnGHActions(t)
+	warsawTz, err := time.LoadLocation("Europe/Warsaw")
+	assertNilF(t, err)
+	runDBTest(t, func(dbt *DBTest) {
+		dbt.mustExec("ALTER SESSION SET TIMEZONE = 'Europe/Warsaw'")
+		forAllStructureTypeFormats(dbt, func(t *testing.T, format string) {
+			testcases := []struct {
+				name      string
+				query     string
+				expected1 any
+				expected2 any
+				actual    any
+			}{
+				{
+					name:      "integer",
+					query:     "SELECT ARRAY_CONSTRUCT(1, 2)::ARRAY(INTEGER) as structured_type UNION SELECT ARRAY_CONSTRUCT(4, 5, 6)::ARRAY(INTEGER)",
+					expected1: []int64{1, 2},
+					expected2: []int64{4, 5, 6},
+					actual:    []int64{},
+				},
+				{
+					name:      "double",
+					query:     "SELECT ARRAY_CONSTRUCT(1.1, 2.2)::ARRAY(DOUBLE) as structured_type UNION SELECT ARRAY_CONSTRUCT(3.3)::ARRAY(DOUBLE)",
+					expected1: []float64{1.1, 2.2},
+					expected2: []float64{3.3},
+					actual:    []float64{},
+				},
+				{
+					name:      "number - fixed",
+					query:     "SELECT ARRAY_CONSTRUCT(1, 2)::ARRAY(NUMBER(38, 0)) as structured_type UNION SELECT ARRAY_CONSTRUCT(3)::ARRAY(NUMBER(38, 0))",
+					expected1: []int64{1, 2},
+					expected2: []int64{3},
+					actual:    []int64{},
+				},
+				{
+					name:      "number - real",
+					query:     "SELECT ARRAY_CONSTRUCT(1.1, 2.2)::ARRAY(NUMBER(38, 19)) as structured_type UNION SELECT ARRAY_CONSTRUCT()::ARRAY(NUMBER(38, 19))",
+					expected1: []float64{1.1, 2.2},
+					expected2: []float64{},
+					actual:    []float64{},
+				},
+				{
+					name:      "string",
+					query:     "SELECT ARRAY_CONSTRUCT('a', 'b')::ARRAY(VARCHAR) as structured_type",
+					expected1: []string{"a", "b"},
+					actual:    []string{},
+				},
+				{
+					name:      "time",
+					query:     "SELECT ARRAY_CONSTRUCT('13:03:02'::TIME, '05:13:22'::TIME)::ARRAY(TIME) as structured_type",
+					expected1: []time.Time{time.Date(0, 1, 1, 13, 3, 2, 0, time.UTC), time.Date(0, 1, 1, 5, 13, 22, 0, time.UTC)},
+					actual:    []time.Time{},
+				},
+				{
+					name:      "date",
+					query:     "SELECT ARRAY_CONSTRUCT('2024-01-05'::DATE, '2001-11-12'::DATE)::ARRAY(DATE) as structured_type",
+					expected1: []time.Time{time.Date(2024, time.January, 5, 0, 0, 0, 0, time.UTC), time.Date(2001, time.November, 12, 0, 0, 0, 0, time.UTC)},
+					actual:    []time.Time{},
+				},
+				{
+					name:      "timestamp_ntz",
+					query:     "SELECT ARRAY_CONSTRUCT('2024-01-05 11:22:33'::TIMESTAMP_NTZ, '2001-11-12 11:22:33'::TIMESTAMP_NTZ)::ARRAY(TIMESTAMP_NTZ) as structured_type",
+					expected1: []time.Time{time.Date(2024, time.January, 5, 11, 22, 33, 0, time.UTC), time.Date(2001, time.November, 12, 11, 22, 33, 0, time.UTC)},
+					actual:    []time.Time{},
+				},
+				{
+					name:      "timestamp_ltz",
+					query:     "SELECT ARRAY_CONSTRUCT('2024-01-05 11:22:33'::TIMESTAMP_LTZ, '2001-11-12 11:22:33'::TIMESTAMP_LTZ)::ARRAY(TIMESTAMP_LTZ) as structured_type",
+					expected1: []time.Time{time.Date(2024, time.January, 5, 11, 22, 33, 0, warsawTz), time.Date(2001, time.November, 12, 11, 22, 33, 0, warsawTz)},
+					actual:    []time.Time{},
+				},
+				{
+					name:      "timestamp_tz",
+					query:     "SELECT ARRAY_CONSTRUCT('2024-01-05 11:22:33 +0100'::TIMESTAMP_TZ, '2001-11-12 11:22:33 +0100'::TIMESTAMP_TZ)::ARRAY(TIMESTAMP_TZ) as structured_type",
+					expected1: []time.Time{time.Date(2024, time.January, 5, 11, 22, 33, 0, warsawTz), time.Date(2001, time.November, 12, 11, 22, 33, 0, warsawTz)},
+					actual:    []time.Time{},
+				},
+				{
+					name:      "bool",
+					query:     "SELECT ARRAY_CONSTRUCT(true, false)::ARRAY(boolean) as structured_type",
+					expected1: []bool{true, false},
+					actual:    []bool{},
+				},
+				{
+					name:      "binary",
+					query:     "SELECT ARRAY_CONSTRUCT(TO_BINARY('616263', 'HEX'), TO_BINARY('646566', 'HEX'))::ARRAY(BINARY) as structured_type",
+					expected1: [][]byte{{'a', 'b', 'c'}, {'d', 'e', 'f'}},
+					actual:    [][]byte{},
+				},
+			}
+			for _, tc := range testcases {
+				t.Run(tc.name, func(t *testing.T) {
+					rows := dbt.mustQuery(tc.query)
+					defer rows.Close()
+					rows.Next()
+					err := rows.Scan(&tc.actual)
+					assertNilF(t, err)
+					if _, ok := tc.actual.([]time.Time); ok {
+						assertEqualE(t, len(tc.actual.([]time.Time)), len(tc.expected1.([]time.Time)))
+						for i := range tc.actual.([]time.Time) {
+							if tc.name == "time" {
+								assertEqualE(t, tc.actual.([]time.Time)[i].Hour(), tc.expected1.([]time.Time)[i].Hour())
+								assertEqualE(t, tc.actual.([]time.Time)[i].Minute(), tc.expected1.([]time.Time)[i].Minute())
+								assertEqualE(t, tc.actual.([]time.Time)[i].Second(), tc.expected1.([]time.Time)[i].Second())
+							} else {
+								assertTrueE(t, tc.actual.([]time.Time)[i].UTC().Equal(tc.expected1.([]time.Time)[i].UTC()))
+							}
+						}
+					} else {
+						assertDeepEqualE(t, tc.actual, tc.expected1)
+					}
+					if tc.expected2 != nil {
+						rows.Next()
+						err := rows.Scan(&tc.actual)
+						assertNilF(t, err)
+						if _, ok := tc.actual.([]time.Time); ok {
+							assertEqualE(t, len(tc.actual.([]time.Time)), len(tc.expected2.([]time.Time)))
+							for i := range tc.actual.([]time.Time) {
+								assertTrueE(t, tc.actual.([]time.Time)[i].UTC().Equal(tc.expected2.([]time.Time)[i].UTC()))
+							}
+						} else {
+							assertDeepEqualE(t, tc.actual, tc.expected2)
+						}
+					}
+					columnTypes, err := rows.ColumnTypes()
+					assertNilF(t, err)
+					assertEqualE(t, len(columnTypes), 1)
+					assertEqualE(t, columnTypes[0].ScanType(), reflect.TypeOf(tc.expected1))
+					assertEqualE(t, columnTypes[0].DatabaseTypeName(), "ARRAY")
+					assertEqualE(t, columnTypes[0].Name(), "STRUCTURED_TYPE")
+				})
+			}
+		})
+	})
+}
+
+func TestArrayWithoutSchema(t *testing.T) {
+	skipStructuredTypesTestsOnGHActions(t)
+	runDBTest(t, func(dbt *DBTest) {
+		forAllStructureTypeFormats(dbt, func(t *testing.T, format string) {
+			rows := dbt.mustQuery("SELECT ARRAY_CONSTRUCT(1, 2)")
+			defer rows.Close()
+			rows.Next()
+			var v string
+			err := rows.Scan(&v)
+			assertNilF(t, err)
+			assertStringContainsE(t, v, "1,\n  2")
+		})
+	})
+}
+
+func TestEmptyArraysAndNullArrays(t *testing.T) {
+	skipStructuredTypesTestsOnGHActions(t)
+	runDBTest(t, func(dbt *DBTest) {
+		forAllStructureTypeFormats(dbt, func(t *testing.T, format string) {
+			rows := dbt.mustQuery("SELECT ARRAY_CONSTRUCT(1, 2)::ARRAY(INTEGER) as structured_type UNION SELECT ARRAY_CONSTRUCT()::ARRAY(INTEGER) UNION SELECT NULL UNION SELECT ARRAY_CONSTRUCT(4, 5, 6)::ARRAY(INTEGER)")
+			defer rows.Close()
+			checkRow := func(rows *RowsExtended, expected *[]int64) {
+				var res *[]int64
+				rows.Next()
+				err := rows.Scan(&res)
+				assertNilF(t, err)
+				assertDeepEqualE(t, res, expected)
+			}
+
+			checkRow(rows, &[]int64{1, 2})
+			checkRow(rows, &[]int64{})
+			checkRow(rows, nil)
+			checkRow(rows, &[]int64{4, 5, 6})
+		})
+	})
+}
+
+func TestArrayWithoutSchemaMetadata(t *testing.T) {
+	skipStructuredTypesTestsOnGHActions(t)
+	runDBTest(t, func(dbt *DBTest) {
+		forAllStructureTypeFormats(dbt, func(t *testing.T, format string) {
+			if format == "NATIVE_ARROW" {
+				t.Skip("Native arrow is not supported in arrays without schema")
+			}
+			rows := dbt.mustQuery("SELECT ARRAY_CONSTRUCT(1, 2) AS structured_type")
+			defer rows.Close()
+			columnTypes, err := rows.ColumnTypes()
+			assertNilF(t, err)
+			assertEqualE(t, len(columnTypes), 1)
+			assertEqualE(t, columnTypes[0].ScanType(), reflect.TypeOf(""))
+			assertEqualE(t, columnTypes[0].DatabaseTypeName(), "ARRAY")
+			assertEqualE(t, columnTypes[0].Name(), "STRUCTURED_TYPE")
+		})
+	})
+}
+
+func TestArrayOfObjects(t *testing.T) {
+	skipStructuredTypesTestsOnGHActions(t)
+	runDBTest(t, func(dbt *DBTest) {
+		forAllStructureTypeFormats(dbt, func(t *testing.T, format string) {
+			rows := dbt.mustQuery("SELECT ARRAY_CONSTRUCT({'s': 's1', 'i': 9}, {'s': 's2', 'i': 8})::ARRAY(OBJECT(s VARCHAR, i INTEGER)) as structured_type UNION SELECT ARRAY_CONSTRUCT({'s': 's3', 'i': 7})::ARRAY(OBJECT(s VARCHAR, i INTEGER))")
+			defer rows.Close()
+			rows.Next()
+			var res []*simpleObject
+			err := rows.Scan(ScanArrayOfScanners(&res))
+			assertNilF(t, err)
+			assertDeepEqualE(t, res, []*simpleObject{{s: "s1", i: 9}, {s: "s2", i: 8}})
+			rows.Next()
+			err = rows.Scan(ScanArrayOfScanners(&res))
+			assertNilF(t, err)
+			assertDeepEqualE(t, res, []*simpleObject{{s: "s3", i: 7}})
+			columnTypes, err := rows.ColumnTypes()
+			assertNilF(t, err)
+			assertEqualE(t, len(columnTypes), 1)
+			assertEqualE(t, columnTypes[0].ScanType(), reflect.TypeOf([]ObjectType{}))
+			assertEqualE(t, columnTypes[0].DatabaseTypeName(), "ARRAY")
+			assertEqualE(t, columnTypes[0].Name(), "STRUCTURED_TYPE")
+		})
+	})
+}
+
+func TestWithHigherPrecision(t *testing.T) {
+	skipStructuredTypesTestsOnGHActions(t)
+	runDBTest(t, func(dbt *DBTest) {
+		forAllStructureTypeFormats(dbt, func(t *testing.T, format string) {
+			if format != "NATIVE_ARROW" {
+				t.Skip("JSON structured type does not support higher precision")
+			}
+			ctx := WithHigherPrecision(context.Background())
+			t.Run("object", func(t *testing.T) {
+				rows := dbt.mustQueryContext(ctx, "SELECT {'i': 10000000000000000000000000000000000000::DECIMAL(38, 0), 'f': 1.2345678901234567890123456789012345678::DECIMAL(38, 37)}::OBJECT(i DECIMAL(38, 0), f DECIMAL(38, 37)) as structured_type")
+				defer rows.Close()
+				rows.Next()
+				var v HigherPrecisionStruct
+				err := rows.Scan(&v)
+				assertNilF(t, err)
+				bigInt, b := new(big.Int).SetString("10000000000000000000000000000000000000", 10)
+				assertTrueF(t, b)
+				assertEqualE(t, bigInt.Cmp(v.i), 0)
+				bigFloat, b := new(big.Float).SetPrec(v.f.Prec()).SetString("1.2345678901234567890123456789012345678")
+				assertTrueE(t, b)
+				assertEqualE(t, bigFloat.Cmp(v.f), 0)
+				columnTypes, err := rows.ColumnTypes()
+				assertNilF(t, err)
+				assertEqualE(t, len(columnTypes), 1)
+				assertEqualE(t, columnTypes[0].ScanType(), reflect.TypeOf(ObjectType{}))
+				assertEqualE(t, columnTypes[0].DatabaseTypeName(), "OBJECT")
+				assertEqualE(t, columnTypes[0].Name(), "STRUCTURED_TYPE")
+			})
+			t.Run("array of big ints", func(t *testing.T) {
+				rows := dbt.mustQueryContext(ctx, "SELECT ARRAY_CONSTRUCT(10000000000000000000000000000000000000)::ARRAY(DECIMAL(38, 0)) as structured_type")
+				defer rows.Close()
+				rows.Next()
+				var v *[]*big.Int
+				err := rows.Scan(&v)
+				assertNilF(t, err)
+				bigInt, b := new(big.Int).SetString("10000000000000000000000000000000000000", 10)
+				assertTrueF(t, b)
+				assertEqualE(t, bigInt.Cmp((*v)[0]), 0)
+				columnTypes, err := rows.ColumnTypes()
+				assertNilF(t, err)
+				assertEqualE(t, len(columnTypes), 1)
+				assertEqualE(t, columnTypes[0].ScanType(), reflect.TypeOf([]*big.Int{}))
+				assertEqualE(t, columnTypes[0].DatabaseTypeName(), "ARRAY")
+				assertEqualE(t, columnTypes[0].Name(), "STRUCTURED_TYPE")
+			})
+			t.Run("array of big floats", func(t *testing.T) {
+				rows := dbt.mustQueryContext(ctx, "SELECT ARRAY_CONSTRUCT(1.2345678901234567890123456789012345678)::ARRAY(DECIMAL(38, 37)) as structured_type")
+				defer rows.Close()
+				rows.Next()
+				var v *[]*big.Float
+				err := rows.Scan(&v)
+				assertNilF(t, err)
+				bigFloat, b := new(big.Float).SetPrec((*v)[0].Prec()).SetString("1.2345678901234567890123456789012345678")
+				assertTrueE(t, b)
+				assertEqualE(t, bigFloat.Cmp((*v)[0]), 0)
+				columnTypes, err := rows.ColumnTypes()
+				assertNilF(t, err)
+				assertEqualE(t, len(columnTypes), 1)
+				assertEqualE(t, columnTypes[0].ScanType(), reflect.TypeOf([]*big.Float{}))
+				assertEqualE(t, columnTypes[0].DatabaseTypeName(), "ARRAY")
+				assertEqualE(t, columnTypes[0].Name(), "STRUCTURED_TYPE")
+			})
+		})
+	})
+}
+
 func forAllStructureTypeFormats(dbt *DBTest, f func(t *testing.T, format string)) {
 	for _, tc := range []struct {
 		name        string