google-bigquery

Length of json array field in Big Query

Given: json string that represents array field selected as a result of query.

How to find out length of JSON array field in Big Query?

Solution

  • See if this does what you want. It uses a JavaScript UDF to parse the JSON and then returns the array length.

    #standardSQL
CREATE TEMP FUNCTION JsonArrayLength(json_array STRING)
RETURNS INT64 LANGUAGE js AS """
var arr = JSON.parse(json_array);
return arr.length;
""";

SELECT JsonArrayLength('[1,3,5,7]');