Skip to content
This repository was archived by the owner on Mar 23, 2026. It is now read-only.

Commit 73c4a73

Browse files
stephaniewang526stephaniewang526
authored
feat: add JSON type support (#1799)
Fixes b/213373577 cc @nageshs
1 parent ca344f5 commit 73c4a73

7 files changed

Lines changed: 168 additions & 1 deletion

File tree

‎google-cloud-bigquery/pom.xml‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -81,6 +81,10 @@
8181
<groupId>org.threeten</groupId>
8282
<artifactId>threetenbp</artifactId>
8383
</dependency>
84+
<dependency>
85+
<groupId>com.google.code.gson</groupId>
86+
<artifactId>gson</artifactId>
87+
</dependency>
8488

8589
<!-- auto-value creates a class that uses an annotation from error_prone_annotations -->
8690
<dependency>

‎google-cloud-bigquery/src/main/java/com/google/cloud/bigquery/LegacySQLTypeName.java‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -93,6 +93,9 @@ public LegacySQLTypeName apply(String constant) {
9393
/** A record type with a nested schema. */
9494
public static final LegacySQLTypeName RECORD =
9595
type.createAndRegister("RECORD").setStandardType(StandardSQLTypeName.STRUCT);
96+
/** Represents JSON data */
97+
public static final LegacySQLTypeName JSON =
98+
type.createAndRegister("JSON").setStandardType(StandardSQLTypeName.JSON);
9699

97100
private static Map<StandardSQLTypeName, LegacySQLTypeName> standardToLegacyMap = new HashMap<>();
98101

‎google-cloud-bigquery/src/main/java/com/google/cloud/bigquery/QueryParameterValue.java‎

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -29,6 +29,7 @@
2929
import com.google.common.collect.ImmutableMap;
3030
import com.google.common.collect.Lists;
3131
import com.google.common.io.BaseEncoding;
32+
import com.google.gson.JsonObject;
3233
import java.io.Serializable;
3334
import java.math.BigDecimal;
3435
import java.util.ArrayList;
@@ -61,6 +62,7 @@
6162
* <li>Float: StandardSQLTypeName.FLOAT64
6263
* <li>BigDecimal: StandardSQLTypeName.NUMERIC
6364
* <li>BigNumeric: StandardSQLTypeName.BIGNUMERIC
65+
* <li>JSON: StandardSQLTypeName.JSON
6466
* </ul>
6567
*
6668
* <p>No other types are supported through that entry point. The other types can be created by
@@ -254,6 +256,22 @@ public static QueryParameterValue string(String value) {
254256
return of(value, StandardSQLTypeName.STRING);
255257
}
256258

259+
/**
260+
* Creates a {@code QueryParameterValue} object with a type of JSON. Currently, this is only
261+
* supported in INSERT, not in query as a filter
262+
*/
263+
public static QueryParameterValue json(String value) {
264+
return of(value, StandardSQLTypeName.JSON);
265+
}
266+
267+
/**
268+
* Creates a {@code QueryParameterValue} object with a type of JSON. Currently, this is only
269+
* supported in INSERT, not in query as a filter
270+
*/
271+
public static QueryParameterValue json(JsonObject value) {
272+
return of(value, StandardSQLTypeName.JSON);
273+
}
274+
257275
/** Creates a {@code QueryParameterValue} object with a type of BYTES. */
258276
public static QueryParameterValue bytes(byte[] value) {
259277
return of(value, StandardSQLTypeName.BYTES);
@@ -347,6 +365,10 @@ private static <T> StandardSQLTypeName classToType(Class<T> type) {
347365
return StandardSQLTypeName.NUMERIC;
348366
} else if (Date.class.isAssignableFrom(type)) {
349367
return StandardSQLTypeName.DATE;
368+
} else if (String.class.isAssignableFrom(type)) {
369+
return StandardSQLTypeName.JSON;
370+
} else if (JsonObject.class.isAssignableFrom(type)) {
371+
return StandardSQLTypeName.JSON;
350372
}
351373
throw new IllegalArgumentException("Unsupported object type for QueryParameter: " + type);
352374
}
@@ -384,6 +406,9 @@ private static <T> String valueToStringOrNull(T value, StandardSQLTypeName type)
384406
break;
385407
case STRING:
386408
return value.toString();
409+
case JSON:
410+
if (value instanceof String || value instanceof JsonObject) return value.toString();
411+
break;
387412
case STRUCT:
388413
throw new IllegalArgumentException("Cannot convert STRUCT to String value");
389414
case ARRAY:

‎google-cloud-bigquery/src/main/java/com/google/cloud/bigquery/StandardSQLTypeName.java‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -56,5 +56,7 @@ public enum StandardSQLTypeName {
5656
/** Represents a year, month, day, hour, minute, second, and subsecond (microsecond precision). */
5757
DATETIME,
5858
/** Represents a set of geographic points, represented as a Well Known Text (WKT) string. */
59-
GEOGRAPHY
59+
GEOGRAPHY,
60+
/** Represents JSON data */
61+
JSON
6062
}

‎google-cloud-bigquery/src/test/java/com/google/cloud/bigquery/QueryParameterValueTest.java‎

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -24,6 +24,7 @@
2424

2525
import com.google.api.services.bigquery.model.QueryParameterType;
2626
import com.google.common.collect.ImmutableMap;
27+
import com.google.gson.JsonObject;
2728
import java.math.BigDecimal;
2829
import java.text.ParseException;
2930
import java.util.Date;
@@ -193,6 +194,24 @@ public void testString() {
193194
assertThat(value.getArrayValues()).isNull();
194195
}
195196

197+
@Test
198+
public void testJson() {
199+
QueryParameterValue value =
200+
QueryParameterValue.json("{\"class\" : {\"students\" : [{\"name\" : \"Jane\"}]}}");
201+
JsonObject jsonObject = new JsonObject();
202+
jsonObject.addProperty("class", "student");
203+
QueryParameterValue value1 = QueryParameterValue.json(jsonObject);
204+
assertThat(value.getValue())
205+
.isEqualTo("{\"class\" : {\"students\" : [{\"name\" : \"Jane\"}]}}");
206+
assertThat(value1.getValue()).isEqualTo("{\"class\":\"student\"}");
207+
assertThat(value.getType()).isEqualTo(StandardSQLTypeName.JSON);
208+
assertThat(value1.getType()).isEqualTo(StandardSQLTypeName.JSON);
209+
assertThat(value.getArrayType()).isNull();
210+
assertThat(value1.getArrayType()).isNull();
211+
assertThat(value.getArrayValues()).isNull();
212+
assertThat(value1.getArrayType()).isNull();
213+
}
214+
196215
@Test
197216
public void testBytes() {
198217
QueryParameterValue value = QueryParameterValue.bytes(new byte[] {1, 3});

‎google-cloud-bigquery/src/test/java/com/google/cloud/bigquery/it/ITBigQueryTest.java‎

Lines changed: 108 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -122,6 +122,7 @@
122122
import com.google.common.collect.Iterables;
123123
import com.google.common.collect.Sets;
124124
import com.google.common.io.BaseEncoding;
125+
import com.google.gson.JsonObject;
125126
import java.io.IOException;
126127
import java.math.BigDecimal;
127128
import java.nio.ByteBuffer;
@@ -707,6 +708,113 @@ public void testCreateTableWithRangePartitioning() {
707708
}
708709
}
709710

711+
@Test
712+
public void testJsonType() throws InterruptedException {
713+
String tableName = "test_create_table_jsontype";
714+
TableId tableId = TableId.of(DATASET, tableName);
715+
Schema schema = Schema.of(Field.of("jsonField", StandardSQLTypeName.JSON));
716+
StandardTableDefinition standardTableDefinition = StandardTableDefinition.of(schema);
717+
try {
718+
// Create a table with a JSON column
719+
Table createdTable = bigquery.create(TableInfo.of(tableId, standardTableDefinition));
720+
assertNotNull(createdTable);
721+
722+
// Insert 4 rows of JSON data into the JSON column
723+
Map<String, Object> jsonRow1 =
724+
Collections.singletonMap(
725+
"jsonField", "{\"student\" : {\"name\" : \"Jane\", \"id\": 10}}");
726+
Map<String, Object> jsonRow2 =
727+
Collections.singletonMap("jsonField", "{\"student\" : {\"name\" : \"Joy\", \"id\": 11}}");
728+
Map<String, Object> jsonRow3 =
729+
Collections.singletonMap(
730+
"jsonField", "{\"student\" : {\"name\" : \"Alice\", \"id\": 12}}");
731+
Map<String, Object> jsonRow4 =
732+
Collections.singletonMap(
733+
"jsonField", "{\"student\" : {\"name\" : \"Bijoy\", \"id\": 14}}");
734+
InsertAllRequest request =
735+
InsertAllRequest.newBuilder(tableId)
736+
.addRow(jsonRow1)
737+
.addRow(jsonRow2)
738+
.addRow(jsonRow3)
739+
.addRow(jsonRow4)
740+
.build();
741+
InsertAllResponse response = bigquery.insertAll(request);
742+
assertFalse(response.hasErrors());
743+
assertEquals(0, response.getInsertErrors().size());
744+
745+
// Query the JSON column with string positional query parameter
746+
String sql =
747+
"SELECT jsonField.class.student.id FROM "
748+
+ tableId.getTable()
749+
+ " WHERE JSON_VALUE(jsonField, \"$.class.student.name\") = ? ";
750+
QueryParameterValue stringParameter = QueryParameterValue.string("Jane");
751+
QueryJobConfiguration queryJobConfiguration =
752+
QueryJobConfiguration.newBuilder(sql)
753+
.setDefaultDataset(DatasetId.of(DATASET))
754+
.setUseLegacySql(false)
755+
.addPositionalParameter(stringParameter)
756+
.build();
757+
TableResult result = bigquery.query(queryJobConfiguration);
758+
for (FieldValueList values : result.iterateAll()) {
759+
assertEquals("10", values.get(0).getValue());
760+
}
761+
762+
// Insert another JSON row parsed from a String with json positional query parameter
763+
String dml = "INSERT INTO " + tableId.getTable() + " (jsonField) VALUES(?)";
764+
QueryParameterValue jsonParameter =
765+
QueryParameterValue.json("{\"class\" : {\"student\" : [{\"name\" : \"Amy\"}]}}");
766+
QueryJobConfiguration dmlQueryJobConfiguration =
767+
QueryJobConfiguration.newBuilder(dml)
768+
.setDefaultDataset(DatasetId.of(DATASET))
769+
.setUseLegacySql(false)
770+
.addPositionalParameter(jsonParameter)
771+
.build();
772+
bigquery.query(dmlQueryJobConfiguration);
773+
Page<FieldValueList> rows = bigquery.listTableData(tableId);
774+
assertEquals(5, Iterables.size(rows.getValues()));
775+
776+
// Insert another JSON row parsed from a JsonObject with json positional query parameter
777+
JsonObject jsonObject = new JsonObject();
778+
jsonObject.addProperty("class", "student");
779+
QueryParameterValue jsonParameter1 = QueryParameterValue.json(jsonObject);
780+
QueryJobConfiguration dmlQueryJobConfiguration1 =
781+
QueryJobConfiguration.newBuilder(dml)
782+
.setDefaultDataset(DatasetId.of(DATASET))
783+
.setUseLegacySql(false)
784+
.addPositionalParameter(jsonParameter1)
785+
.build();
786+
bigquery.query(dmlQueryJobConfiguration1);
787+
Page<FieldValueList> rows1 = bigquery.listTableData(tableId);
788+
assertEquals(6, Iterables.size(rows1.getValues()));
789+
int rowCount = 0;
790+
for (FieldValueList row : rows1.iterateAll()) {
791+
FieldValue jsonCell = row.get(0);
792+
if (rowCount == 1) assertEquals("{\"class\":\"student\"}", jsonCell.getStringValue());
793+
rowCount++;
794+
}
795+
796+
// Try inserting a malformed JSON
797+
QueryParameterValue badJsonParameter =
798+
QueryParameterValue.json("{\"class\" : {\"student\" : [{\"name\" : \"BadBoy\"}}");
799+
QueryJobConfiguration dmlQueryJobConfiguration2 =
800+
QueryJobConfiguration.newBuilder(dml)
801+
.setDefaultDataset(DatasetId.of(DATASET))
802+
.setUseLegacySql(false)
803+
.addPositionalParameter(badJsonParameter)
804+
.build();
805+
try {
806+
bigquery.query(dmlQueryJobConfiguration2);
807+
fail("Querying with malformed JSON shouldn't work");
808+
} catch (BigQueryException e) {
809+
BigQueryError error = e.getError();
810+
assertNotNull(error);
811+
assertEquals("invalidQuery", error.getReason());
812+
}
813+
} finally {
814+
assertTrue(bigquery.delete(tableId));
815+
}
816+
}
817+
710818
@Test
711819
public void testCreateTableWithConstraints() {
712820
String tableName = "test_create_table_with_constraints";

‎pom.xml‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -93,6 +93,12 @@
9393
<version>${google-api-services-bigquery.version}</version>
9494
</dependency>
9595

96+
<dependency>
97+
<groupId>com.google.code.gson</groupId>
98+
<artifactId>gson</artifactId>
99+
<version>2.8.9</version>
100+
</dependency>
101+
96102
<!-- Test dependencies -->
97103
<dependency>
98104
<groupId>junit</groupId>

0 commit comments

Comments
 (0)