JSON data type in Teradata with examples

What is JSON?

Javascript Object Notation (JSON) is a text based data format for representing structured data based on Javascript object syntax. It is often used when data is sent from server to webpage. It may contains text, curly braces, square brackets, colons, commas, double quotes, and maybe a few other characters.

JSON example

JSON data type in Teradata

Teradata providing the the JSON data type to store JSON data in tables. Teradata Database can store JSON records as a JSON document or store JSON records in relational format.Also it provides methods, functions, and stored procedures that operate on the JSON data type, such as parsing and validation.

JSON data type syntax

We can specify the JSON as a datatype for the columns while creating the table in Teradata. The following syntax will be used to define a table column to be JSON type.

  • integer – It specifies the maximum length in characters of the JSON data type.
    If we not specify a maximum length, the default maximum length for the character set is used.
  • CHARACTER SET – The character set for the JSON type can be UNICODE or LATIN. If we not specify a character set, the default character set for the user is used.
  • attributes – The following data type attributes are supported for the JSON type.
    • NULL and NOT NULL
    • FORMAT
    • TITLE
    • NAMED

JSON data type examples in Teradata

Lets create the Employee table with the two column such as Employee_id and details. Here we are defining the details column as JSON data type as below.

#1.Insert the JSON value to a table

The JSON value should be covered with single quotes while inserting into the table.Otherwise Teradata will throw the error code 3704 with the message as “It is not a valid Teradata SQL token”.

Example :

#2.Select the JSON value in a Table

If we select the table which contains the JSON value, It will show in the form of text document in the Teradata SQL assistant.

JSON data type in Teradata
JSON data type in Teradata

Let’s select the JSON value based on the key column in the JSON text.

Example : Retrieving the JSON value from Teradata

select or fetch the JSON value from the Teradata table
Example for Fetch the JSON value in Teradata

Also we can use JSONExtract method to get the Json value based on the key column.But again it will return the results in the form of text document in Teradata SQL assistant.

#3.Modifying JSON columns in Teradata

The following UPDATE statement sets the details column with the new json value.

Example for UPDATE statement

Lets select the Employee table to see the updated JSON value. Previously the employee_id #1892 is assigned to employee which has the first name as James. After the update statement, the new JSON value has updated and employee_id #1892 is assigned to “Justin”.

JSON update statement example in Teradata

JSON update statement example in Teradata

#4.Using JSON type in Delete statment

We can specify the JSON value in the delete statement using JSONExtractValue function. We need to cast the JSON value to predefined type that can have relational comparisons performed on it,

The following DELETE statement deletes the record which has age as 31 in the Json value.

Example :

Lets run the select statement to see the remaining records in the employee table.

Delete statement for the JSON value
JSON delete statement example in Teradata