JSON functions

When you enter your query in the SQL Editor or create a calculation, you use Presto SQL syntax. To adjust JavaScript Object Notation (JSON) arrays and values, you may find these common functions useful.

JSON_ARRAY_LENGTH

To return the number of values — or the length — of a JSON array, use the JSON_ARRAY_LENGTH function, such as:

json_array_length(json) → bigint

For example, SELECT json_array_length('[a, b, c]'); returns a length of 3.

JSON_ARRAY_GET

To return a value of a JSON array based on a zero-based index, use the JSON_ARRAY_GET function, such as:

json_array_get(json_array, index) → json

For example:

• SELECT json_array_get('["a", "b", "c"]', 0); returns 'a'
• SELECT json_array_get('["a", "b", "c"]', 1); returns 'b'

To return values indexed from the end of the array, you can use negative indexes. For example:

• SELECT json_array_get('["c", "b", "a"]', -1); returns 'a'
• SELECT json_array_get('["c", "b", "a"]', -2); returns 'b'

If no element exists at the specified index, the function returns null. For example:

• SELECT json_array_get('[]', 0); returns null
• SELECT json_array_get('["a", "b", "c"]', 10); returns null
• SELECT json_array_get('["c", "b", "a"]', -10); returns null

JSON_EXTRACT

To evaluate the JSONPath-like expression of string that contains JSON and return the results as a string encoded as JSON, use the JSON_EXTRACT function, such as:

json_extract(json, json_path) → json

For example:

SELECT json_extract(json, '$.store.book');

Note: To return the results as a string not encoded as JSON, use the JSON_EXTRACT_SCALAR function instead.

JSON_EXTRACT_SCALAR

To evaluate the JSONPath-like expression of string that contains JSON and references a scalar — text, boolean, or number —  value, and return the results as a string, use the JSON_EXTRACT_SCALAR function, such as:

json_extract_scalar(json, json_path) → varchar

For example:

SELECT json_extract_scalar(json, '$.store.book[0].author');

SELECT json_extract_scalar('[1, 2, 3]', '$[2]');

Note: To return the results as a string encoded as JSON, use the JSON_EXTRACT function instead.

JSON_FORMAT

To return the values of JSON syntax as a string, use the JSON_FORMAT function, such as:

json_format(json) → varchar

For example:

• SELECT json_format(json '[1, 2, 3]'); returns '[1, 2, 3]'
• SELECT json_format(json '"a"'); returns '"a"'

Tip: The JSON_FORMAT function is the inverse of the JSON_PARSE function.

JSON_PARSE

To parse a string as JSON syntax, use the JSON_PARSE function, such as:

json_parse(string) → json

For example, SELECT json_parse('[1, 2, 3]'); returns JSON '[1, 2, 3]'

Tip: The JSON_PARSE function is the inverse of the JSON_FORMAT function.

W_JSON_ARRAY_JOIN

To return the values of a JSON array as a delimited string, use the W_JSON_ARRAY_JOIN function, such as:

w_json_array_join(array, 'delimiter') → varchar

Tip: If you use an ESG Program as a query source, this function can return its metrics' related ESG content for a specific framework. For example, if a metric aligns with Global Reporting Initiative (GRI) Standards, SELECT w_json_array_join(json_extract(program.frameworks, '$["gri"]'), ','); returns the values of the array {"gri":["301", "302", "303"]} as 301, 302, 303.