SAFE.PARSE_JSON(…) to Test for Valid JSON

1. Supports the standard is json test

The safe.parse_json function returns the JSON value if the argument is valid JSON. Otherwise it returns the SQL null value.

Similar to the is json test of standard SQL [safe.]parse_json allows objects to contain the same key several times.

{"a": 42,
 "a": 1}

Related

Product Documentation

• Google BigQuery: JSON functions: PARSE_JSON

Standard SQL Features

• T821, “Basic SQL/JSON query operators”

  • … Is Json [Array|Object|Scalar]: Test for Valid JSON, Distinguish between Arrays, Objects and Scalars

  • T822, “SQL/JSON: IS JSON WITH UNIQUE KEYS predicate”

• T832, “SQL/JSON path language: item method”

  • .type() item method

• T851, “SQL/JSON: optional keywords for default syntax”

Non-Standard Alternatives

• json_valid() function

• isjson() function

Tutorials

• JSON in SQL:2016

Normative References

The safe.parse_json function is not covered by the international SQL standard ISO/IEC 9075-2:2023.