JSON_INTEGER

Description

JSON_INTEGER

is a row function that extracts an

INTEGER

value from a field in a JSON object.

JSON_INTEGER

(

json_string

, "

json_field

Returns one value per row of type

INTEGER

json_string: Required. The name of a field or expression of type
TEXT
(or a literal string) that contains a valid JSON object.
json_field: Required. The key or name of the field value you want to extract.; For top-level fields, specify the name identifier (key) of the field.; To access fields within a nested object, specify a dot-separated path of field names (for example top_level_field_name.nested_field_name
).; To extract a value from an array, specify the dot-separated path of field names and the array position starting at 0 for the first value in an array, 1 for the second value, and so on (for example, field_name.0
).; If the name identifier contains dot or period characters within the name itself, escape the name by enclosing it in brackets (for example, [field.name.with.dot
].[another.dot.field.name
]; If the field name is null (empty), use brackets with nothing in between as the identifier, for example
[]
.

If you had an address
field that contained a JSON object formatted like this:

{"street_address":"123 B Street", "city":"San Mateo", "state":"CA", "zip_code":"94403"}

You could extract the zip_code
value using the expression, which returns

"94403"

JSON_INTEGER([address], "zip_code")

If you had a top_scores
field that contained a JSON object formatted like this (with the values contained in an array):

{"practice_scores":["538","674","1021"], "test_scores":["753","957","1032"]}

You could extract the third value of the test_scores
array using the expression, which returns

"1032"

JSON_INTEGER([top_scores], "test_scores.2")