UDF Return JSON Dot Notation Example | JSON Data Type | Teradata Vantage - Example: UDF Returning a Value Using Dot Notation (Level 1) - Advanced SQL Engine - Teradata Database

JSON Data Type

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
gzn1554761068186.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1150
lifecycle
previous
Product Category
Teradata Vantageā„¢

This UDF shows the use of dot notation syntax to retrieve the value of "thenum" 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.thenum;
SELECT test_udf(new json('{ "thenum" : "10" , "name": {"firstname" : "abc" }}'));

Result:

test_udf( NEW JSON('{ "thenum" : "10" , "name": {"firstname"
---------------------------------------------------------------------------
10
CREATE TABLE test_data(x1 int, y1 json(32000));

INSERT INTO test_data(1,new json('{ "thenum" : "10" , "name": {"firstname" : "abc" }}'));
SELECT test_udf(y1) FROM test_data;

Result:

test_udf(y1)
---------------------------------------------------------------------------
10
SELECT test_udf(test_data.y1) FROM test_data;

Result:

test_udf(y1)
---------------------------------------------------------------------------
10
SELECT test_udf(test_database.test_data.y1) FROM test_data;

Result:

test_udf(y1)
---------------------------------------------------------------------------
10