The JSON datatype in BigQuery

The JSON datatype in BigQuery. This topic has been sitting in my Notion list of post ideas for some time.

So in our beloved BQ it is a native, standalone data type, not just another STRING 😁, although strings can of course hold json-like strings.

As data engineers we typically consume them in our pipelines, but let's first understand how to create them.

There are a couple of ways to express a JSON value:
- using the JSON literal
- by parsing a json-like STRING with JSON_PARSE()
- from SQL objects (including an entire row) with TO_JSON()
- creating a json_object from key-value pairs with JSON_OBJECT()
- creating a JSON_ARRAY() from BQ ARRAY

Note that, for some of the above options, since JSON also have quotes, use multi-line strings """ """ or escape quotes with \.

Defining our JSON objects as such will allow us to use JSON functions with them and, of course, store heterogeneous data in the same column.

Stay tuned for the next posts on this topic.

WITH input_data AS (

SELECT

  JSON """
      {"name": "John Doe",
      "city": "New York",
      "sports": ["football", "snooker", "tennis"]}
  """
  AS json_native,

  "{\"name\": \"John Doe\", \"city\": \"New York\", \"sports\": [\"football\", \"snooker\", \"tennis\"]}" AS json_string,

  "John Doe" AS name,
  "New York" AS city,
  ["football", "snooker", "tennis"] AS sports
)

SELECT
  json_native,
  PARSE_JSON(json_string) AS parsed_json_from_string,
  TO_JSON(STRUCT(name, city, sports)) AS json_from_key_values,
  JSON_OBJECT('name', name, 'city', city, 'sports', sports) AS json_object_from_key_value_pairs,
  JSON_ARRAY(sports) AS json_array_from_array

FROM  input_data

---

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

• JSON datatype vs JSON-like STRING in BigQuery
• Flattening JSON arrays in BigQuery
• Extracting keys from JSON in BigQuery
• LAX JSON conversion functions in BigQuery