Skip to main content Link Menu Expand (external link) Document Search Copy Copied

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, and
  • import/ingest actions are faster because there are less rows to import.

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

Syntax

  (FLATTEN(<colname>))

Arguments

Argument Description Data type Required?
<colname> SET or SETQ column SET or SETQ Yes

Returns

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

Source column type Returned data type
IDSET/IDSETQ ID (unsigned integer)
STRINGSET/STRINGSETQ String

Additional information

GROUP BY issues on 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));