FROM_JSON

​

Syntax

​

Scalar

from_json(json_string, schema [, options])

​

Arguments

​

Returns

• Type: Derived from schema; see the type restrictions in Limitations
• NULL behavior: NULL, empty/invalid rows produce NULL. Supported field-level type conflicts can become NULL fields rather than discard an otherwise valid struct.
• Shape: STRUCT fields and nested ARRAY/MAP shapes are derived from the literal schema. Missing/invalid fields can be NULL.
• Ordering: Array value order is preserved by decoding; map/object-key ordering is not a SQL ordering guarantee.

​

Properties

​

Usage notes

• The constant schema argument determines the result type.
• The options argument is accepted but has no effect on parsing.
• Struct fields follow the declared schema; missing fields become nullable values, and arrays/maps retain their declared element/value types.
• Deterministic: The same inputs and settings produce the same result.

​

Examples

​

Typical behavior

SELECT from_json('{"a":7}', 'a INT').a AS "value";

​

Parse fields with different types

SELECT from_json('{"name":"Ada","active":true}', 'name STRING, active BOOLEAN') AS person;

​

Limitations

• Options such as FAILFAST, corrupt-record handling, primitivesAsString, dateFormat, and timestampFormat do not change parsing behavior.
• Nested values can be converted only where the source and target types are compatible with the supported JSON conversions.
• Declared decimal precision and scale are not always preserved. Decimal declarations with precision at most 19 can become BIGINT or DOUBLE; generic DECIMAL/NUMERIC becomes DECIMAL(38, 18), and HUGEINT becomes DECIMAL(38, 0). Do not rely on the schema to preserve an exact decimal representation.

​

Related functions

• to_json
• parse_json