schema_of_json function

Applies to: Databricks SQL Databricks Runtime

Returns the schema of a JSON string in DDL format.

Syntax

schema_of_json(jsonStr [, options] )

Arguments

• jsonStr: A STRING expression with a JSON string.
• options: An optional MAP literals with keys and values being STRING.

Returns

A STRING holding a definition of an array of structs with n fields of strings where the column names are derived from the JSON keys. The field values hold the derived formatted SQL types. For a complete list of options, see JSON.

To derive the aggregated schema of a group of JSON strings use the schema_of_json_agg aggregate function.

Examples

> SELECT schema_of_json('[{"col":0}]');
 ARRAY<STRUCT<`col`: BIGINT>>

> SELECT schema_of_json('[{"col":01}]', map('allowNumericLeadingZeros', 'true'));
 ARRAY<STRUCT<`col`: BIGINT>>

Related functions

• from_json function
• schema_of_json_agg aggregate function