schema_of_json function

Applies to: check marked yes Databricks SQL check marked yes 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 details on options, see from_json function.

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>>