Computes the virtual columns (keys and values) from flex table VMap data.

Computes the virtual columns (keys and values) from flex table VMap data. Use this function to compute keys without creating an associated table view. To also build a view, use COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type



COMPUTE_FLEXTABLE_KEYS ('[[database.]schema.]flex-table')



Database and schema. The default schema is public. If you specify a database, it must be the current database.

Name of the flex table.


The function stores its results in a table named flex-table_keys. The table has the following columns:

Column Description
KEY_NAME The name of the virtual column (key). Keys larger than 65,000 bytes are truncated.
FREQUENCY The number of times the key occurs in the VMap.
DATA_TYPE_GUESS Estimate of the data type for the key based on the non-null values found in the VMap. The function determines the type of each non-string value, depending on the length of the key, and whether the key includes nested maps. If the EnableBetterFlexTypeGuessing configuration parameter is 0 (OFF), this function instead treats all flex table keys as string types ([LONG] VARCHAR or [LONG] VARBINARY).

COMPUTE_FLEXTABLE_KEYS sets the column width for keys to the length of the largest value for each key multiplied by the FlexTableDataTypeGuessMultiplier factor.


In the following example, JSON data with consistent fields has been loaded into a flex table. Had the data been more varied, you would see different numbers of occurrences in the keys table:

 Please see public.reviews_flex_keys for updated keys
(1 row)

SELECT * FROM reviews_flex_keys;
  key_name   | frequency | data_type_guess
 user_id     |      1000 | Varchar(44)
 useful      |      1000 | Integer
 text        |      1000 | Varchar(9878)
 stars       |      1000 | Numeric(5,2)
 review_id   |      1000 | Varchar(44)
 funny       |      1000 | Integer
 date        |      1000 | Timestamp
 cool        |      1000 | Integer
 business_id |      1000 | Varchar(44)
(9 rows)

See also