FLATTEN() hint

FLATTEN() is supported on FeatureBase Cloud

The FLATTEN() hint is used to return distinct or group on individual members of IDSET and STRINGSET columns. It can be used for:

• SELECT…WITH…GROUP BY queries
• SELECT DISTINCT… queries
• queries with one input argument that matches the sole DISTINCT/GROUP BY column

Before you begin

• SQL SELECT statement
• SET and SETQ data types

Syntax

[DISTINCT
  (FLATTEN(<colname>))]
[WITH
  (FLATTEN(<colname>))
GROUP BY
  (<colname>)]

Arguments

Returns

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

Examples

create table segments  
    (_id id, segment stringset);  

insert into segments(_id, segment)  
    values (1, ['RED', 'BLUE', 'GREEN']),
      (2, ['GREEN']),
      (3, ['RED', 'BLUE', 'GREEN']);

GROUP BY with flatten()

This query can also be run as a SELECT…GROUP BY statement

Count individual values from the segments table

SELECT count(*) AS cnt, segment FROM segments
WITH (flatten(segment))
group by segment;

 cnt | segment
-----+-----------
   2 | ['RED']
   2 | ['BLUE']
   3 | ['GREEN']