Spark SQL - Return JSON Array Length (json_array_length)

Spark SQL function json_array_length returns the number of elements in the outmost JSON array of the JSON array. The input can be a valid JSON array string too. If the input is not valid, it returns NULL.

Code snippet

spark-sql> SELECT json_array_length('["a","b","c"]');
  3
spark-sql> SELECT json_array_length('[1,2,3,4');
  NULL
spark-sql> SELECT json_array_length('{}');
  NULL
spark-sql> SELECT json_array_length('{"key1":1,"key2":2}');
  NULL
spark-sql> SELECT json_array_length('[{"key1":1},{"key2":2},{"key3":3},{"k41":4,"k42":[5,6]}]');
  4

infoThis function is only available from Spark 3.1.0.

Other references

Spark SQL - Extract Value from JSON String

Spark SQL - Convert Object to JSON String

Spark SQL - Return JSON Object Keys (json_object_keys)

Code snippet

Other references

In this article