FLATTEN() hint

In a typical database, each field – the intersection of row and column – contains an individual value.

FeatureBase SET and SETQ data types are designed to contain an array of comma-separated values within each field.

This means:

• data does not need to be normalized into separate tables,
• import/ingest actions are faster because there are less rows to import,
• queries are faster

Queries on SET and SETQ data return unexpected results:

• SELECT...DISTINCT returns the number of fields, not the number of unique values
• SELECT...GROUP BY returns the array of values in each field

The FLATTEN() hint overcomes this issue.

Before you begin

• SQL SELECT statement
• SET and SETQ data types

Syntax

  (FLATTEN(<colname>))

Arguments

Returns

Individual values are returned from the specified column based on the source data type

Additional information

GROUP BY issues on SET and SETQ data types

• GROUP BY issues for SET and SETQ data types

Examples

CREATE TABLE demo-table (_id id, hobby stringset, income int);

INSERT INTO demo-table VALUES
  (0, ['running', 'biking', 'swimming'], 80000),
  (1, ['biking'], 100000);

A SELECT query returns:

SELECT * FROM demo-table;
 _id | hobby                             | income
-----+-----------------------------------+--------
   0 | ['running', 'biking', 'swimming'] |  80000
   1 | ['biking']                        | 100000

Unexpected results

Both queries return results based on the number of fields, rather than the results themselves.

SELECT hobby, sum(income) FROM demo-table GROUP BY hobby;
 hobby                             |        
-----------------------------------+--------
 ['biking']                        | 100000
 ['running', 'biking', 'swimming'] |  80000

SELECT COUNT(DISTINCT hobby) FROM demo-table;

Expected results

Use the FLATTEN() hint to return expected results.

SELECT hobby, sum(income) FROM demo-table WITH (flatten(hobby)) GROUP BY hobby;

SELECT COUNT(DISTINCT hobby) FROM demo-table WITH (FLATTEN(hobby));