Skip to main content
Version: current [25.x]

Categories: Conversion

TRY_CONVERT_FROM

Attempts to convert a JSON string to a data type supported in Dremio.

Syntax​

TRY_CONVERT_FROM(value_to_convert STRING AS data_type name_of_type) → ANY​

  • value_to_convert: Either a STRING literal containing a JSON string or a VARCHAR column containing a JSON object.
  • data_type: A ROW or ARRAY data type specification to use for the conversion. Supports conversion to complex types only.

Examples

Returns an ARRAY from the JSON 
SELECT TRY_CONVERT_FROM('["apple", "strawberry", "banana"]' AS VARCHAR ARRAY)
-- ["apple", "strawberry", "banana"]
Returns a ROW from the JSON 
SELECT TRY_CONVERT_FROM('{"name":"Gnarly", "age":7, "car":null}' AS ROW(name VARCHAR, age INT))
-- {"name:"Gnarly","age":7}
Returns null because the JSON cannot be converted 
SELECT TRY_CONVERT_FROM('["apple", "strawberry", "banana"]' AS ROW(name VARCHAR, age INT))
-- null

Usage Notes​

These coercion rules apply for nested fields in the target ROW or ARRAY data type specification. If the JSON string cannot be converted to the desired output type based on these rules, null is returned.

Supported Field-Level Coercions

Target Field TypeValid JSON Values
INT, BIGINTJSON whole number or JSON string containing a whole number. A decimal point will disallow the coercion, even if there are only zeros after the decimal. Examples: 1, “1”
FLOAT, DOUBLE, DECIMALJSON number or JSON string containing a number. Examples: 1, 1.1, “1.1”
BOOLEANJSON true or false value or JSON string containing “true” or “false”.
VARCHARAny JSON boolean, number, or string value. JSON objects or lists cannot be converted to VARCHAR.
DATE, TIME, TIMESTAMPValid values must match these formats:
  • DATE: yyyy-MM-dd
  • TIME: HH:mm:ss.SSS
  • TIMESTAMP: yyyy-MM-dd HH:mm:ss.SSS
Due to the format restrictions, we recommend to leave date/time values as VARCHAR and convert the result of TRY_CONVERT_FROM to the target type using TO_DATE, TO_TIME, or TO_TIMESTAMP functions.
ROWAny JSON object value. Fields not present in both the target type and the JSON object will be ignored. An empty struct value {} will be returned when there are no common fields between the JSON object and the target type.
ARRAYAny JSON list value. JSON lists with mixed primitive type values are supported. JSON lists containing mixed primitive and complex types are not supported and cannot be converted.