Nulls and sort order

String parsing/formatting

Be explicit about formats when working with String types. A common issue is the decimal separator, which differs between environments: an EU locale typically uses ,, a US locale uses ..

See Formatting numbers and dates for Format, Length, Precision, Decimal, Group and Currency.

A Boolean field holds true, false or null.

Reading text as a Boolean

Writing a Boolean as text

This applies wherever Hop turns a Boolean into text: Write to log, the preview grid, text files, Concat fields and so on.

A Boolean Format is the text for true, a slash, and the text for false, for example true/false, Y/N, 1/0 or yes/no. The mask must contain exactly one slash, so neither text can contain a slash itself. Spaces around the slash are ignored, and the two texts must differ in more than upper and lower case. The Format column offers the common masks for Boolean fields, and you can type any other pair, for example Ja/Nein to write German words. See Your own true and false text.

Why two Boolean fields can print differently

The length is often set for you rather than by you. Converting a String field to a Boolean, for example in the Meta-data tab of Select Values, keeps the String’s length. A true/false column read from a database with length 4 therefore becomes a Boolean of length 4 and prints true, while a Boolean read by JSON Input has no length and prints Y. Both values are the same true.

Set a Boolean Format on the fields to get the same text everywhere, whatever their length.

A Timestamp value can be created from a Date, String, Number, or another Timestamp.

Conversions

UUID values map to java UUID object that stores them as 16-byte type. Storing a UUID with the String type uses 32-byte. The difference in storage is noticeable in many situations like inserting in a database with native UUID support.

When using MongoDB, Hop writes UUIDs using the STANDARD representation, which corresponds to BSON Binary subtype 4.

Sorting

Databases may order UUID columns by their raw binary value (as is the case in PostgreSQL). Hop, however, compares UUIDs by their two 64-bit halves as signed longs — first the most significant bits (MSB), then the least significant bits (LSB). These ordering methods can differ. To ensure consistent results, perform all sorting either within Hop or entirely in the database.

Gotchas

Writing a UUID to a database without native support as a UUID type (e.g. MySQL) will fail. Convert them to String before writing.

The JSON type in Hop follows the official JSON standard: https://www.json.org/json-en.html. It is treated as an unordered set of key/value pairs. This means that JSON values may behave differently from their String representations. For example, consider these two JSON objects:

When treated as String, they are different. When treated as JSON, they are considered equal. Because JSON comparison in Hop performs structural checks, it is generally more robust — but also slightly slower — than string comparison.

Sorting

The way Hop sorts JSON values resembles how PostgreSQL sorts JSONB values. Keys are sorted alphabetically, and values are compared in the following type order:

Arrays are compared element by element; if all compared elements are equal, the longer array is considered greater.

MongoDB

The JSON type supports all standard JSON values when reading or writing to MongoDB. Values that are part of MongoDB’s extended JSON (for example, Date objects) are not supported. To fully support MongoDB-specific BSON values, use the String type instead.

The Vector type holds a dense floating point vector, as produced by an embedding model and consumed by a vector store. Values are stored as a float[].

The canonical text form is a bracketed, comma separated list of numbers:

That form is both valid JSON and the literal syntax pgvector accepts, so a vector survives a round trip through a text file, a Data Grid, a JSON document or a database column without a conversion step in between.

Conversions

Vector converts to and from String using the canonical text form above. Conversion from other types is not supported: convert to String first if an intermediate type is involved.

Databases

When Hop generates DDL, a vector field becomes the database’s own vector column wherever there is one:

Whether the type is available is decided per server rather than per database product. Oracle and SQL Server grew their vector type at a known release, so the dialect compares the version the driver reports. On PostgreSQL the type comes from the pgvector extension, which is installed per database, so the dialect looks for the type in the list the driver reports instead of comparing versions. A connection to a database without the extension therefore writes a text column, and so does a server older than the release that introduced the type. Generating DDL without a connection keeps the native type, because an unknown server is not treated as a server that lacks it.

A database with no vector type of its own writes a text column, which holds the canonical form above. That is also what a vector without a dimension falls back to where the native type has no unsized spelling.

Hop sends the value in whatever form the database accepts, which is not always the text form: PostgreSQL needs it as an unspecified type so the server applies the column’s own input function, MySQL takes its storage format of one little endian float32 per dimension, and CrateDB takes a float array. Oracle, SQL Server and DuckDB accept the text form as it stands. None of this is configurable, and none of it changes what a pipeline looks like.

Dimensions

The field’s length carries the number of dimensions, the way it carries the size of a String. A length of zero or less means the dimension is unspecified.

Hop never pads or truncates a vector to match a declared length. An embedding resized to fit would still return results from a vector store, and those results would be wrong, so a mismatch is left for the store to reject.

Gotchas

The type does not itself enforce the number of dimensions. A vector store will reject a value whose length does not match the column it is written to, so the dimensions of the embedding model and of the target column have to be kept aligned by the pipeline author.