Examples: SELECT AS JSON - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

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}