JSON_DECIMAL
Description
JSON_DECIMAL
is a row function that extracts a NUMERIC
value from a field in a JSON object.Syntax
JSON_DECIMAL
(json_string
, "json_field
")Return Value
Returns one value per row of type
NUMERIC
.Input Parameters
- json_string
- Required. The name of a field or expression of typeTEXT(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 exampletop_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[].
Examples
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.67","674.99","1021.52"], "test_scores":["753.21","957.88","1032.87"]}
You could extract the third value of the
test_scores
array using the expression, which returns "1032.87"
: JSON_DECIMAL([top_scores], "test_scores.2")