Examples | ExtractValue Method | Teradata Vantage - Examples - Advanced SQL Engine - Teradata Database

Example: Returning Values for a Query Not Properly Expressed in Dot Notation (AVRO)

Populate the myAVROTable06 table:

avro08b.data
7B2274797065223A227265636F7264222C226E616D65223A227265635F30222C226669656C6473223A5B7B226E616D65223A2253616C65222C2274797065223A7B2274797065223A227265636F7264222C226E616D65223A227265635F31222C226669656C6473223A5B7B226E616D65223A224974656D5F4944222C2274797065223A22696E74227D2C7B226E616D65223A224974656D5F4E616D65222C2274797065223A22737472696E67227D2C7B226E616D65223A224974656D5F436F6C6F72222C2274797065223A22737472696E67227D2C7B226E616D65223A224974656D5F5374796C65222C2274797065223A22737472696E67227D2C7B226E616D65223A225175616E746974795F507572636861736564222C2274797065223A22696E74227D2C7B226E616D65223A224974656D5F5072696365222C2274797065223A22646F75626C65227D2C7B226E616D65223A22546F74616C5F5072696365222C2274797065223A22646F75626C65227D5D7D7D5D7D006E0E62696379636C650672656408626F79730200000000000059400000000000005940
CREATE TABLE myAVROTable06(
	id INTEGER,
	avroFile DATASET STORAGE FORMAT Avro);
.import vartext file avro08b.data
USING (c1 VARBYTE(1000))
INSERT INTO myAVROTable06(1,:c1);

Then, return values by using the ExtractValue method. You also can use dot notation to express the return values.

SELECT avroFile.ExtractValue('$.Sale.Item_ID') FROM myAVROTable09;
	> 55

	SELECT avroFile.ExtractValue('$.Sale[Item_ID,Item_Name]') FROM myAVROTable09;
	> [55,"bicycle"]

Example: Returning Values for a Query Not Properly Expressed in Dot Notation (CSV)

Populate the myCSVTable06 table:

CREATE TABLE myCSVTable06(
	id INTEGER,
	csvFile DATASET STORAGE FORMAT CSV CHARACTER SET LATIN);
INSERT INTO myCSVTable06(
  0,
  new DATASET('{"field_delimiter" : "&",
                "record_delimiter" : "#",
                "field_names" : ["Item_ID","Item_Name","Price"]}',
                '55&bicycle&89.99',
                CSV,
                   LATIN));

Then, return values by using the ExtractValue method.

SELECT csvFile.ExtractValue('$[0].Item_ID') FROM myCSVTable06;
 > 55

SELECT csvFile.ExtractValue('$..Item_ID,Item_Name') FROM myCSVTable06;
 > "55","bicycle"