Datawise
BigQuery JSON

Determining JSON types in BigQuery

Constantin LunguUpdated 1 min read

Photo by Pankaj Patel on Unsplash

Here's a mildly interesting function if you're working with JSON in BigQuery.

JSON_TYPE takes in a JSON value and returns the name of the respective JSON type (object, array, string, number, boolean, null) as a STRING.

See below an illustration of it in action.

Also, given we use the native JSON datatype, notice how we can just access the first ([0]) element in an ARRAY or a field directly by dot notation.
This you cannot do with a JSON-like STRING (not without parsing). Check out my previous post about JSON vs JSON-like string.

DECLARE json_data  DEFAULT JSON
"""
[
 {"city": "New York", "age": 25,"name": "John Doe", "registered_footballer": true },
 {"city": "London", "age": 21, "name": "Jane Dew", "registered_footballer": false},
 {"city": "Berlin", "age": 30, "name": "Joanna Dow", "registered_footballer": true},
 {"city": "Prague", "age": 28, "name": "Johann Duw", "registered_footballer": false}
]
""";

SELECT

  JSON_TYPE(json_data), -- array,
  JSON_TYPE(json_data[0]), -- object,
  JSON_TYPE(json_data[0].name), -- string,
  JSON_TYPE(json_data[0].age), -- number,
  JSON_TYPE(json_data[0].registered_footballer) --boolean

BigQuery results: one row with f0_ array, f1_ object, f2_ string, f3_ number and f4_ boolean.

Enjoyed this? Here are some related articles you might find useful: