SQL map and struct functions

Build typed nested values in Haybarn SQL and retrieve their fields or keys. Choose structs for named fields and maps for key-value lookup.

Choose a function

A struct gives each field a name and its own type. A map uses one key type and one value type for its entries. Choose the representation that reflects how the data will be queried.

A missing map key and a key with a NULL value can return the same lookup result. Check key membership separately when that distinction affects your logic.

Look up a map value by key

Read the value associated with a key in a MAP. This returns the value directly, rather than wrapping it in a list.

A missing key returns NULL. A key whose stored value is NULL also returns NULL.

Look up a map value by key
SQL
Haybarn WASM 2.0.0-rc12 · In your browser

Guide example · Run to view results.

Expected result
quantitymissing
12NULL

Watch for: Use map_contains() when you must distinguish a missing key from a present key with a NULL value.

Full map_extract_value() reference →

All maps & structs functions

25 functions in Haybarn WASM 2.0.0-rc12. Browse the filtered catalog →

  • map_concat()

    Returns a map created from merging the input maps, on key collision the value is taken from the last map with that key

  • map_contains()

    Checks if a map contains a given key.

  • map_contains_entry()
  • map_contains_value()
  • map_entries()

    Returns the map entries as a list of keys/values

  • map_extract()

    Returns a list containing the value for a given key or an empty list if the key is not contained in the map. The type of the key provided in the second parameter must match the type of the map’s keys else an error is returned

  • map_extract_value()

    Read the value associated with a key in a MAP. This returns the value directly, rather than wrapping it in a list.

  • map_from_entries()

    Returns a map created from the entries of the array

  • map_keys()

    Returns the keys of a map as a list

  • map_values()

    Returns the values of a map as a list

  • struct_concat()

    Merge the multiple STRUCTs into a single STRUCT.

  • struct_contains()

    Check if an unnamed STRUCT contains the value.

  • struct_extract()

    Extract the named entry from the STRUCT.

  • struct_extract_at()

    Extract the entry from the STRUCT by position (starts at 1!).

  • struct_has()

    Check if an unnamed STRUCT contains the value.

  • struct_indexof()

    Get the position of the entry in an unnamed STRUCT, starting at 1.

  • struct_insert()

    Adds field(s)/value(s) to an existing STRUCT with the argument values. The entry name(s) will be the bound variable name(s)

  • struct_keys()

    Returns the field names of a STRUCT as a list

  • struct_pack()

    Combine named values into a typed STRUCT, using argument names as field names and preserving each field’s type.

  • struct_position()

    Get the position of the entry in an unnamed STRUCT, starting at 1.

  • struct_update()

    Changes field(s)/value(s) to an existing STRUCT with the argument values. The entry name(s) will be the bound variable name(s)

  • struct_values()

    Returns the field values of a STRUCT as an UnnamedStruct

  • union_extract()

    Extract the value with the named tags from the union. NULL if the tag is not currently selected

  • union_tag()

    Retrieve the currently selected tag of the union as an ENUM

  • union_value()

    Create a single member UNION containing the argument value. The tag of the value will be the bound variable name