BigQuery Introduces JSON_KEYS Function to Extract Unique Keys

Google has announced the introduction of the JSON_KEYS function in BigQuery, currently available in Preview. This new function extracts unique keys from a JSON expression, offering developers more tools for JSON data manipulation.

Key features of JSON_KEYS

1. Function Signature: JSON_KEYS(json_expr[, max_depth[, mode]])

2. Arguments:

   • json_expr: JSON input
   • max_depth: Maximum depth of nested fields to search (optional)
   • mode: Determines key extraction behavior (optional)

Example:

SELECT JSON_KEYS(JSON '{"a": {"b":1}}', 1) AS json_keys

3. Modes:

   • strict (default): Ignores keys in arrays
   • lax: Includes keys in non-consecutively nested arrays
   • lax recursive: Returns all keys

4. Return Type: ARRAY

5. Behavior:

   • Returns de-duplicated keys in alphabetical order
   • Excludes array indices
   • Escapes keys with special characters using double quotes
   • Maintains case sensitivity

6. Null Handling:

   • Returns NULL if json_expr or mode is NULL
   • Ignores max_depth if it's NULL

Additionally, Google has introduced mode options for some JSON functions that use JSONPath, allowing for more flexible matching of JSON data structures.

This update aims to enhance BigQuery's JSON processing capabilities, potentially simplifying complex data operations for developers working with JSON-structured data.