Package dev.frostlake.functions.table

Class Flatten

java.lang.Object
dev.frostlake.functions.TableFunction
dev.frostlake.functions.table.Flatten
public class Flatten extends TableFunction
FLATTEN — expands semi-structured data into one row per element.

Returns SEQ, KEY, PATH, INDEX, VALUE and THIS. INPUT must be VARIANT, OBJECT or ARRAY: Snowflake will not read JSON out of a VARCHAR, and answers invalid type [VARCHAR(7)] for parameter 'INPUT' for FLATTEN(INPUT => '[1,2,3]') — PARSE_JSON is the supported spelling. That refusal, the argument names and the boolean-ness of OUTER / RECURSIVE are all settled while the statement is compiled (see TableFunctionArguments); what is left here is what a value decides.

The measured row rules:

  • SEQ numbers the INPUT RECORD, not the element: every row of one call carries the same SEQ, starting at 1.
  • Only a container expands. A VARIANT holding a scalar — a string, a number, a JSON null — contributes NO rows, so FLATTEN(PARSE_JSON('"abc"')) is empty rather than one row, and a string that merely LOOKS like JSON is not re-read as JSON.
  • RECURSIVE descends only through what the MODE emitted. Under MODE => 'ARRAY' an object's members are neither emitted nor followed, which is why FLATTEN(PARSE_JSON('{"arr":[1,2,3]}'), MODE => 'ARRAY', RECURSIVE => TRUE) is empty.
  • PATH prefixes the reported path rather than replacing it: PATH => 'a.b' reports a.b[0]. A path that matches nothing, or matches a scalar, yields no rows.
  • OUTER's stand-in row is all NULL. For an empty container it still reports the container as THIS and an empty PATH; for a scalar or NULL input both are NULL.

  • Constructor Summary

    Constructors
    Constructor
    Description
    Flatten()
     

  • Method Summary

    Modifier and Type
    Method
    Description
    ResultSet
    execute(List<Object> positionalArgs)
    The positional form fills the same five parameters in order — FLATTEN(input, path, outer, recursive, mode) — and each one it is given counts, so a positional PATH selects a sub-element exactly as the named one does.
    ResultSet
    execute(Map<String,Object> namedArgs)
    Execute the table function with named arguments
    void
    validateArgs(Map<String,Object> namedArgs)
    What is left once the statement has compiled: the argument NAMES and the missing-INPUT refusal are raised from the parse tree, where they carry a position, so only the MODE value is judged here — live spells it Bad flattening mode 'NOPE' (not 'BOTH', 'ARRAY', or 'OBJECT').

    Methods inherited from class dev.frostlake.functions.TableFunction

    getName

    Methods inherited from class java.lang.Object

    clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

  • Constructor Details

    • Flatten

      public Flatten()

  • Method Details

    • execute

      public ResultSet execute(List<Object> positionalArgs)
      The positional form fills the same five parameters in order — FLATTEN(input, path, outer, recursive, mode) — and each one it is given counts, so a positional PATH selects a sub-element exactly as the named one does.
      Overrides:
      execute in class TableFunction

    • execute

      public ResultSet execute(Map<String,Object> namedArgs)
      Description copied from class: TableFunction
      Execute the table function with named arguments
      Specified by:
      execute in class TableFunction

    • validateArgs

      public void validateArgs(Map<String,Object> namedArgs)
      What is left once the statement has compiled: the argument NAMES and the missing-INPUT refusal are raised from the parse tree, where they carry a position, so only the MODE value is judged here — live spells it Bad flattening mode 'NOPE' (not 'BOTH', 'ARRAY', or 'OBJECT').
      Specified by:
      validateArgs in class TableFunction