15.10 - query expression - Teradata Database

The following describes the query expression variables.

"queryexpr" :

Required, literal entry.

Must be lowercase.

"temp_column_name"

Any user-defined name for the temporary column. The temporary column name must be unique or an error is reported.

The names are not case sensitive. For example, col1 and COL1 will fail because they are used in internal queries and are not unique.

You cannot use JSONID and ROWINDEX (uppercase or lowercase) here.

"column_type"

Data type of the temp_column_name column.

The queryexpr is used to define the data types of the data selected in the input query, for the columns that are not the ID value or the JSON object. It is mandatory that the extra columns be referenced in the queryexpr to specify the data type desired.

The data types in the queryexpr should match the actual data type of the columns specified in the input query. No explicit cast is added, so the data must be implicitly castable to the data type defined in the queryexpr, if not the exact data type. Any errors encountered will result in a failed shred, and are reported to the user.

The following example demonstrates the importance of the ordering. Notice that both columns qrycol1 and qrycol2 are included in the queryexpr. Note, qrycol2 in the queryexpr refers to qrycol1 in the input query, and qrycol1 in the queryexpr refers to qrycol2 in the input query. As stated, order is significant, not the names.

CALL SYSLIB.JSON_SHRED_BATCH(
'SELECT JSONDOCID, JSONDT1, qrycol1, qrycol2 FROM jsonshred.JSON_TABLE3 WHERE JSONID=100',
'[ { "rowexpr" : "$.population.profile",
     "colexpr" : [{"col1" : "$.name.first", "type" : "VARCHAR(30)"},
                   {"col2" : "$.address.zip", "type" : "NUMBER(5,0)"}],
     "queryexpr" : [{ "qrycol2" : "VARCHAR(20)"}, { "qrycol1" : "VARCHAR(20)"}],
     "tables" : [
                  {"jsonshred.JSON_SHRED_TABLE1" : {
                     "metadata" : { "operation" : "insert" },
                     "columns" : {"EmpID":"JSONID*10", "NAME":"col1","STATE":"qrycol1", "ZIP":"col2"}
                     }
                  }
                ]         
   }
]',res );

JSONID and ROWINDEX (uppercase or lowercase) are not allowed in colexpr and queryexpr because they are fixed temporary column names. A syntax error is reported if they are used in those clauses.