jsonextractkey
This section contains reference documentation for the JSONEXTRACTKEY function.
Extracts all matched JSON field keys based on 'jsonPath' into a STRING_ARRAY.
Signature
JSONEXTRACTKEY(jsonField, 'jsonPath', [paramString])
jsonField
An Identifier/Expression contains JSON documents.
'jsonPath'
Follows JsonPath Syntax to read values from JSON documents.
'paramString'
Parameter-based configuration for advanced options using semicolon-delimited key-value pairs. - MAXDEPTH: Controls maximum extraction depth to prevent overly deep traversals - Example: jsonExtractKey(json, '$..', 'MAXDEPTH=2') - Extract keys up to 2 levels deep - Default: Unlimited depth (Integer.MAX_VALUE) - Non-positive values treated as unlimited - DOTNOTATION: Toggle between JsonPath and dot notation output formats - Example: jsonExtractKey(json, '$..', 'DOTNOTATION=true') - JsonPath format: $['a']['b']['c'] - Dot notation: a.b.c - Default: false (JsonPath format)
Usage Examples
The examples in this section are based on the Batch JSON Quick Start. In particular we'll be querying the row WHERE id = 7044874109.
select id, repo, JSONEXTRACTKEY(repo, '$.*') AS keys
from githubEvents
WHERE id = 70448741097044874109
{"id":115911530,"name":"LimeVista/Tapes","url":"https://api.github.com/repos/LimeVista/Tapes"}
["$['id']", "$['name']", "$['url']"]
select id, repo, JSONEXTRACTKEY(repo, '$.*', 'dotNotation=true') AS keys
from githubEvents
WHERE id = 70448741097044874109
{"id":115911530,"name":"LimeVista/Tapes","url":"https://api.github.com/repos/LimeVista/Tapes"}
["id", "name", "url"]
More examples
Last updated
Was this helpful?

