JSON_STRING

Description

JSON_STRING

is a row function that extracts a

TEXT

value from a field in a JSON object.

JSON_STRING

(

json_string

, "

json_field

Returns one value per row of type

TEXT

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":"94403"}

You could extract the state
value using the expression:

JSON_STRING([address], "state")

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

{"hobbies":["sailing","hiking","cooking"], "interests":["art","music","travel"]}

You could extract the first value of the hobbies
array using the expression, which returns

"sailing"

JSON_STRING([misc], "hobbies.0")