Views and Path Filtering for JSON | Native Object Store ( NOS ) | Teradata Vantage - 17.05 - Using Views and Path Filtering - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Native Object Store Getting Started Guide

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

There are specific benefits to creating foreign table views, in addition to the usual reasons of security and hiding SQL complexity. These benefits include:

  • Simplified payload attribute names. You can replace attributes named with dot notation with descriptive names.
  • Makes it easier to query object store data with inconsistent uppercase and lowercase field names. JSON field names are case sensitive, but Teradata SQL is case insensitive.
  • You can hide the $path references in a foreign table and make path names look like standard column names.

Follow these recommendations when creating a view on a foreign table:

  • Include both path names and payload attributes as columns in the view.
  • CAST all values in the view to an appropriate data type.
  • It is often the case that the path segment name (such as siteno in siteno / year / month /day) is also represented by an attribute in your data. When this is true, Teradata recommends you equate them in the WHERE expression in the view and only specify one of them in the column list. For example, see the use of TheSite in the example view here: Using a View with Path Filtering.