feat(bigquery): support JSON as a data type (#5986)

* feat(bigquery): support JSON as a data type
googleapis · Jul 26, 2022 · 835fe4f · 835fe4f
1 parent 38af8ae
commit 835fe4f
Show file tree

Hide file tree

Showing 7 changed files with 70 additions and 2 deletions.
diff --git 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/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.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/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/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
@@ -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 {

diff --git 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)
  }