Datawise
BigQuery JSON

JSON datatype vs JSON-like STRING in BigQuery

Constantin LunguUpdated 1 min read

Photo by Michael Dziedzic on Unsplash

In one my previous posts, we've briefly introduced the JSON datatype in BigQuery.

But did anyone notice how most of the JSON functions have signatures for both a JSON-type input and a json-formatted string input?

What is the difference between the two?

Well, I like to call the json-formatted STRING a "json-like string" because while it might look like it, it's not necessarily valid JSON.

When you use, say, JSON_VALUE to query such a string, it does not validate it and reads it from the start until (and if) it finds the key matching your query. It does not care that you gave it invalid JSON.

In the example below, the json-formatted/json-like string is missing a closing bracket "}", but JSON_VALUE using it still manages to retrieve the 'key' since it never reaches the missing bracket .

DECLARE native_json DEFAULT  JSON
"""
{"key": "value"}
"""
;

DECLARE json_formatted_string STRING DEFAULT "{\"key\": \"value\"";



SELECT
  JSON_VALUE(native_json, '$.key') AS value_from_native,
  JSON_VALUE(json_formatted_string, '$.key') AS value_from_json_like_string

BigQuery results: value_from_native and value_from_json_like_string both return value.

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