Joining on multivalue fields (Comparison Rules)

You can specify fields that are lists, maps or variants as Join keys (see Join Types) just like any other fields. The only question is when two fields are equal.

A list/map/variant can:

  • be null - its value is not specified;
map[string, date] myMap; // a just-declared map - no keys, no values
  • contain empty elements
string[] myList = ["hello", ""]; // a list whose second element is empty
variant singleValue = "hello"; // variant containing string
variant multiValue = ["hello", "world"]; // variant containing list of strings

Two maps/lists/variants are equal if both of them are not null, they have the same data type, element count and all element values (keys-values in maps) are equal.

Two maps/lists/variants are not equal if either of them is null.

Warning!

When comparing two lists, the order of their elements has to match, too. In maps and variants, there is no 'order' of elements, therefore you cannot use them in Sort key.

Example 9. Integer lists which are (not) equal - symbolic notation

[1,2] == [1,2]
[null] != [1,2]
[1] != [1,2]
null != null // two unspecified lists
[null] == [null] // an extra case: lists which are not empty but whose elements are null