Comparative Study of JSON Processing Methods in MaxCompute

Background: Processing large JSON fields in MaxCompute often requires extracting specific parts or generating new JSON structures.

Methods for JSON extraction compared: FROM_JSON , get_json_object , and custom UDFs based on JMESPath or JSONPath.

Selection criteria: simple field extraction (use get_json_object ); need to retain the original JSON or apply complex logic (use JMESPath); other cases (use FROM_JSON ).

Performance test on a table with 3,484,503 rows shows extraction speeds (rec/ms): get_json_object 146.951 (head) / 70.277 (tail), custom JMESPath UDFs ~15‑16, FROM_JSON 46.958 (head) / 29.239 (tail). Simple extraction is fastest, while complex logic incurs higher cost.

Recommendations: use FROM_JSON or JMESPath for complex queries, get_json_object for straightforward field or array‑index extraction.

JSON generation methods in MaxCompute: TO_JSON , custom UDFs ( bi_to_json_string , bi_to_json_string_ex , bi_put_json_values , bi_merge_json_array_distinct , bi_sort_json_array ), and JMESPath‑based concatenation.

Example of TO_JSON :

SELECT to_json(named_struct('a',1,'b',2));

Example of a custom UDF for merging values:

SELECT bi_put_json_values('{"fav":[12,3,4]}','name',bi_to_json_string_ex(name),'age',bi_to_json_string_ex(age));

Example of JMESPath merge to add a field to each array element:

SELECT jmespath_get_json('[{"a":1,"b":"b0"},{"a":1,"b":"b1"}]','map(&merge(@,{"c":1}),@)');

Conclusion: choose the method based on whether you need to generate JSON from scratch, modify shallow structures, or perform conditional, deep transformations.