This UDF shows the use of dot notation syntax to retrieve the value of "name.firstname.lastname" from the JSON string.
REPLACE FUNCTION test_udf(string JSON(32000)) RETURNS VARCHAR(32000) LANGUAGE SQL CONTAINS SQL DETERMINISTIC SQL SECURITY DEFINER COLLATION INVOKER INLINE TYPE 1 RETURN string.name.firstname.lastname;
SELECT test_udf(new json('{ "thenum" : "10" , "name": {"firstname" : { "lastname":"abc"} }}'));
Result:
test_udf( NEW JSON('{ "thenum" : "10" , "name": {"firstname" --------------------------------------------------------------------------- abc
CREATE TABLE test_data(x1 int, y1 json(32000)); INSERT INTO test_data(1,new json('{ "thenum" : "10" , "name": {"firstname" : { "lastname":"abc"} }}'));
SELECT test_udf(y1) FROM test_data;
Result:
test_udf(y1) --------------------------------------------------------------------------- abc
SELECT test_udf(test_data.y1) FROM test_data;
Result:
test_udf(y1) --------------------------------------------------------------------------- abc
SELECT test_udf(test_database.test_data.y1) FROM test_data;
Result:
test_udf(y1) --------------------------------------------------------------------------- abc