JSON datatype vs JSON-like STRING in BigQuery
Senior Data Engineer • Contractor / Freelancer • GCP & AWS Certified
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 .
Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.
Enjoyed this? Here are some related articles you might find useful:
