17.05 - Examples: SELECT AS JSON - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

Following is the table definition for these examples.

CREATE TABLE MyTable (
a INTEGER, 
b INTEGER, 
j JSON AUTO COLUMN);

These statements insert data into the table.

INSERT INTO MyTable JSON '{"a":10,"b":1234,"extra":"1234"}';
INSERT INTO MyTable JSON '{"a":2345,"b":11,"extra":"2222"}';

This SELECT statement returns all of the columns for MyTable, ordered by the first column.

SELECT * FROM MyTable ORDER BY 1;
a b j
10 1234 Snippet:{"extra":"1234"}
2345 11 Snippet:{"extra":"2222"}

This statement includes the AS JSON option to return the data from columns a, b, and j in JSON format.

SELECT AS JSON a, b, j FROM MyTable;

The output is a single JSON column titled "JSON".

JSON
Snippet:{"a":2345,"b":11,"j":{"extra":"2222"}}
Snippet:{"a":10,"b":1234,"j":{"extra":"1234"}}

For SELECT AS JSON with the ORDER BY option, you must specify the column by name, not position.

SELECT AS JSON a, b FROM MyTable ORDER BY a ASC;
JSON
Snippet:{"a":2345,"b":11,"j":{"extra":"2222"}}
Snippet:{"a":10,"b":1234,"j":{"extra":"1234"}}

For SELECT AS JSON with the TOP n option, you must specify the ORDER BY column by name, not position.

SELECT AS JSON TOP 2 a, b FROM MyTable ORDER BY a DESC;
JSON
Snippet:{"a":2345,"b":11}
Snippet:{"a":10,"b":1234}