Repository: googleapis/google-cloud-go
Pull request #5986 (2022-05-04 00:17:02)

feat(bigquery): support JSON as a data type


Patch:
diff --git a/bigquery/nulls.go b/bigquery/nulls.go
index 6ef53d13fc..cbe4fa34dc 100644
--- a/bigquery/nulls.go
+++ b/bigquery/nulls.go
@@ -60,6 +60,14 @@ type NullGeography struct {
 
 func (n NullGeography) String() string { return nullstr(n.Valid, n.GeographyVal) }
 
+// NullJSON represents a BigQuery JSON string that may be NULL.
+type NullJSON struct {
+	JSONVal string
+	Valid   bool // Valid is true if JSONVal is not NULL.
+}
+
+func (n NullJSON) String() string { return nullstr(n.Valid, n.JSONVal) }
+
 // NullFloat64 represents a BigQuery FLOAT64 that may be NULL.
 type NullFloat64 struct {
 	Float64 float64
@@ -147,6 +155,9 @@ func (n NullString) MarshalJSON() ([]byte, error) { return nulljson(n.Valid, n.S
 // MarshalJSON converts the NullGeography to JSON.
 func (n NullGeography) MarshalJSON() ([]byte, error) { return nulljson(n.Valid, n.GeographyVal) }
 
+// MarshalJSON converts the NullJSON to JSON.
+func (n NullJSON) MarshalJSON() ([]byte, error) { return nulljson(n.Valid, n.JSONVal) }
+
 // MarshalJSON converts the NullTimestamp to JSON.
 func (n NullTimestamp) MarshalJSON() ([]byte, error) { return nulljson(n.Valid, n.Timestamp) }
 
@@ -268,6 +279,20 @@ func (n *NullGeography) UnmarshalJSON(b []byte) error {
 	return nil
 }
 
+// UnmarshalJSON converts JSON into a NullJSON.
+func (n *NullJSON) UnmarshalJSON(b []byte) error {
+	n.Valid = false
+	n.JSONVal = ""
+	if bytes.Equal(b, jsonNull) {
+		return nil
+	}
+	if err := json.Unmarshal(b, &n.JSONVal); err != nil {
+		return err
+	}
+	n.Valid = true
+	return nil
+}
+
 // UnmarshalJSON converts JSON into a NullTimestamp.
 func (n *NullTimestamp) UnmarshalJSON(b []byte) error {
 	n.Valid = false
@@ -350,6 +375,7 @@ var (
 	typeOfNullBool      = reflect.TypeOf(NullBool{})
 	typeOfNullString    = reflect.TypeOf(NullString{})
 	typeOfNullGeography = reflect.TypeOf(NullGeography{})
+	typeOfNullJSON      = reflect.TypeOf(NullJSON{})
 	typeOfNullTimestamp = reflect.TypeOf(NullTimestamp{})
 	typeOfNullDate      = reflect.TypeOf(NullDate{})
 	typeOfNullTime      = reflect.TypeOf(NullTime{})
@@ -368,6 +394,8 @@ func nullableFieldType(t reflect.Type) FieldType {
 		return StringFieldType
 	case typeOfNullGeography:
 		return GeographyFieldType
+	case typeOfNullJSON:
+		return JSONFieldType
 	case typeOfNullTimestamp:
 		return TimestampFieldType
 	case typeOfNullDate:
diff --git a/bigquery/params.go b/bigquery/params.go
index 3c8f0f55be..33c7993ee2 100644
--- a/bigquery/params.go
+++ b/bigquery/params.go
@@ -78,6 +78,7 @@ var (
 	bigNumericParamType = &bq.QueryParameterType{Type: "BIGNUMERIC"}
 	geographyParamType  = &bq.QueryParameterType{Type: "GEOGRAPHY"}
 	intervalParamType   = &bq.QueryParameterType{Type: "INTERVAL"}
+	jsonParamType       = &bq.QueryParameterType{Type: "JSON"}
 )
 
 var (
@@ -171,6 +172,8 @@ func paramType(t reflect.Type) (*bq.QueryParameterType, error) {
 		return stringParamType, nil
 	case typeOfNullGeography:
 		return geographyParamType, nil
+	case typeOfNullJSON:
+		return jsonParamType, nil
 	}
 	switch t.Kind() {
 	case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64, reflect.Uint8, reflect.Uint16, reflect.Uint32:
@@ -243,7 +246,8 @@ func paramValue(v reflect.Value) (*bq.QueryParameterValue, error) {
 		typeOfNullTimestamp,
 		typeOfNullDate,
 		typeOfNullTime,
-		typeOfNullDateTime:
+		typeOfNullDateTime,
+		typeOfNullJSON:
 		// Shared:  If the Null type isn't valid, we have no value to send.
 		// However, the backend requires us to send the QueryParameterValue with
 		// the fields empty.
@@ -261,6 +265,8 @@ func paramValue(v reflect.Value) (*bq.QueryParameterValue, error) {
 			res.Value = fmt.Sprint(v.FieldByName("StringVal").Interface())
 		case typeOfNullGeography:
 			res.Value = fmt.Sprint(v.FieldByName("GeographyVal").Interface())
+		case typeOfNullJSON:
+			res.Value = fmt.Sprint(v.FieldByName("JSONVal").Interface())
 		case typeOfNullFloat64:
 			res.Value = fmt.Sprint(v.FieldByName("Float64").Interface())
 		case typeOfNullBool:
@@ -388,6 +394,7 @@ var paramTypeToFieldType = map[string]FieldType{
 	bigNumericParamType.Type: BigNumericFieldType,
 	geographyParamType.Type:  GeographyFieldType,
 	intervalParamType.Type:   IntervalFieldType,
+	jsonParamType.Type:       JSONFieldType,
 }
 
 // Convert a parameter value from the service to a Go value. This is similar to, but
@@ -432,6 +439,8 @@ func convertParamValue(qval *bq.QueryParameterValue, qtype *bq.QueryParameterTyp
 				return NullTime{Valid: false}, nil
 			case "GEOGRAPHY":
 				return NullGeography{Valid: false}, nil
+			case "JSON":
+				return NullJSON{Valid: false}, nil
 			}
 
 		}
diff --git a/bigquery/schema.go b/bigquery/schema.go
index 4b090e0632..559f68f65c 100644
--- a/bigquery/schema.go
+++ b/bigquery/schema.go
@@ -244,6 +244,8 @@ const (
 	BigNumericFieldType FieldType = "BIGNUMERIC"
 	// IntervalFieldType is a representation of a duration or an amount of time.
 	IntervalFieldType FieldType = "INTERVAL"
+	// JSONFieldType is a representation of a json object.
+	JSONFieldType FieldType = "JSON"
 )
 
 var (
@@ -263,6 +265,7 @@ var (
 		GeographyFieldType:  true,
 		BigNumericFieldType: true,
 		IntervalFieldType:   true,
+		JSONFieldType:       true,
 	}
 	// The API will accept alias names for the types based on the Standard SQL type names.
 	fieldAliases = map[FieldType]FieldType{
diff --git a/bigquery/value.go b/bigquery/value.go
index 84e55acaae..8077aee5bf 100644
--- a/bigquery/value.go
+++ b/bigquery/value.go
@@ -178,6 +178,14 @@ func setGeography(v reflect.Value, x interface{}) error {
 	return nil
 }
 
+func setJSON(v reflect.Value, x interface{}) error {
+	if x == nil {
+		return errNoNulls
+	}
+	v.SetString(x.(string))
+	return nil
+}
+
 func setBytes(v reflect.Value, x interface{}) error {
 	if x == nil {
 		v.SetBytes(nil)
@@ -309,6 +317,18 @@ func determineSetFunc(ftype reflect.Type, stype FieldType) setFunc {
 			}
 		}
 
+	case JSONFieldType:
+		if ftype.Kind() == reflect.String {
+			return setJSON
+		}
+		if ftype == typeOfNullJSON {
+			return func(v reflect.Value, x interface{}) error {
+				return setNull(v, x, func() interface{} {
+					return NullJSON{JSONVal: x.(string), Valid: true}
+				})
+			}
+		}
+
 	case BytesFieldType:
 		if ftype == typeOfByteSlice {
 			return setBytes
@@ -960,6 +980,8 @@ func convertBasicType(val string, typ FieldType) (Value, error) {
 		return Value(r), nil
 	case GeographyFieldType:
 		return val, nil
+	case JSONFieldType:
+		return val, nil
 	case IntervalFieldType:
 		i, err := ParseInterval(val)
 		if err != nil {


Test patch:
diff --git a/bigquery/nulls_test.go b/bigquery/nulls_test.go
index 7e5c939a14..7e9b1f0054 100644
--- a/bigquery/nulls_test.go
+++ b/bigquery/nulls_test.go
@@ -39,6 +39,7 @@ func TestNullsJSON(t *testing.T) {
 		{&NullBool{Valid: true, Bool: true}, `true`},
 		{&NullString{Valid: true, StringVal: "foo"}, `"foo"`},
 		{&NullGeography{Valid: true, GeographyVal: "ST_GEOPOINT(47.649154, -122.350220)"}, `"ST_GEOPOINT(47.649154, -122.350220)"`},
+		{&NullJSON{Valid: true, JSONVal: "{\"foo\": \"bar\"}"}, `"{\"foo\": \"bar\"}"`},
 		{&NullTimestamp{Valid: true, Timestamp: testTimestamp}, `"2016-11-05T07:50:22.000000008Z"`},
 		{&NullDate{Valid: true, Date: testDate}, `"2016-11-05"`},
 		{&NullTime{Valid: true, Time: nullsTestTime}, `"07:50:22.000001"`},
@@ -49,6 +50,7 @@ func TestNullsJSON(t *testing.T) {
 		{&NullBool{}, `null`},
 		{&NullString{}, `null`},
 		{&NullGeography{}, `null`},
+		{&NullJSON{}, `null`},
 		{&NullTimestamp{}, `null`},
 		{&NullDate{}, `null`},
 		{&NullTime{}, `null`},
diff --git a/bigquery/params_test.go b/bigquery/params_test.go
index 366184894a..6be55301d0 100644
--- a/bigquery/params_test.go
+++ b/bigquery/params_test.go
@@ -119,6 +119,8 @@ var scalarTests = []struct {
 	{&IntervalValue{Years: 1, Months: 2, Days: 3}, false, "1-2 3 0:0:0", intervalParamType, &IntervalValue{Years: 1, Months: 2, Days: 3}},
 	{NullGeography{GeographyVal: "POINT(-122.335503 47.625536)", Valid: true}, false, "POINT(-122.335503 47.625536)", geographyParamType, "POINT(-122.335503 47.625536)"},
 	{NullGeography{Valid: false}, true, "", geographyParamType, NullGeography{Valid: false}},
+	{NullJSON{Valid: true, JSONVal: "{\"alpha\":\"beta\"}"}, false, "{\"alpha\":\"beta\"}", jsonParamType, "{\"alpha\":\"beta\"}"},
+	{NullJSON{Valid: false}, true, "", jsonParamType, NullJSON{Valid: false}},
 }
 
 type (
diff --git a/bigquery/value_test.go b/bigquery/value_test.go
index 4d08336ed0..3bd7961586 100644
--- a/bigquery/value_test.go
+++ b/bigquery/value_test.go
@@ -38,6 +38,7 @@ func TestConvertBasicValues(t *testing.T) {
 		{Type: NumericFieldType},
 		{Type: BigNumericFieldType},
 		{Type: GeographyFieldType},
+		{Type: JSONFieldType},
 	}
 	row := &bq.TableRow{
 		F: []*bq.TableCell{
@@ -49,6 +50,7 @@ func TestConvertBasicValues(t *testing.T) {
 			{V: "123.123456789"},
 			{V: "99999999999999999999999999999999999999.99999999999999999999999999999999999999"},
 			{V: testGeography},
+			{V: "{\"alpha\": \"beta\"}"},
 		},
 	}
 	got, err := convertRow(row, schema)
@@ -58,7 +60,7 @@ func TestConvertBasicValues(t *testing.T) {
 
 	bigRatVal := new(big.Rat)
 	bigRatVal.SetString("99999999999999999999999999999999999999.99999999999999999999999999999999999999")
-	want := []Value{"a", int64(1), 1.2, true, []byte("foo"), big.NewRat(123123456789, 1e9), bigRatVal, testGeography}
+	want := []Value{"a", int64(1), 1.2, true, []byte("foo"), big.NewRat(123123456789, 1e9), bigRatVal, testGeography, "{\"alpha\": \"beta\"}"}
 	if !testutil.Equal(got, want) {
 		t.Errorf("converting basic values: got:\n%v\nwant:\n%v", got, want)
 	}