How to find the length of hive json array field using hive query

Question

"keys": [ "2324", "abc"] 

This is one of the key of hive JSON. I want to find the length of the "keys" field. i am reading the JSON using get_json_object() method.

This is the way I am doing but getting error:

select count(*) from table_name where json_array_length(get_json_object(node,'$.keys'))=2;

Answer 1

since you are looking into an json array, you could simply do a split and then apply size to get the desired result.

select size(split(get_json_object(data,'$.keys'),',')) from <table name>;

Answer 2

get_json_object returns string. Remove square brackets and split to get array, use size to get array size:

size(split(regexp_replace(get_json_object(node,'$.keys'),'\\[|\\]',''),'", '))