Feature differences

The following features are available in both standard and DSQL maps but have different behaviors.

Indexes in loops and substring functions

In standard maps, you can refer to a loop index using the LoopIndex function. In this case, the index of the first occurrence is 1. For more information, see LoopIndex.

In DSQL maps, you can refer to a loop index using the INDEX keyword in a FROM clause. In this case, the index of the first occurrence is 0. For more information, see Using the FROM clause.

The same difference also applies to the substring functions. In the standard Substring function, the index of the first character is 1, while in the DSQL subString function, the index of the first character is 0.

Grouping

In standard maps, you can group elements using the Distinct feature in the SimpleLoop function. This feature returns the groups in the order in which the grouping element values were found in the input data. For more information, see SimpleLoop.

In DSQL maps, you can group elements with the GROUP BY clause. The groups are returned in an undefined order. For more information, see Using the GROUP BY clause.

Unrolled and split loops

In DSQL maps, when you have unrolled or split a loop, the new loops created do not support the following features:

LET and SET clauses to define a variable
Conditional expressions

These features can still be used on the original loop.

String formatting

When an input element has a null value and the output element is a string marked as not nullable, standard maps return an empty string. In DSQL maps, the Nullable attribute is ignored and a null value is returned.

Number formatting

When the input data does not match a numeric constraint, standard maps produce a warning and continue processing, while DSQL maps produce an exception and stop processing.
When a numeric value is formatted as a string with a fixed size in the output, the value is right-padded with spaces in standard maps, and left-padded with zeroes in DSQL maps.
In DSQL maps, numeric expressions involving fractions are handled using the Float data type. When the output element is defined as a Decimal without an explicit scale, the result preserves the scale of the Float. This is not the case for standard maps. For example, if the result of the expression is 0.20000000298023224, the DSQL map returns this value, while the standard map returns 0.2. You can use the formatNumber function in DSQL maps to format the output.

Empty nested arrays

Standard maps and DSQL maps behave differently when they encounter an empty array while iterating on nested arrays.

For example, with the following JSON input, the goal is to flatten it to get an array of records, each containing one last name and one first name.

[
	{
		"lastname" : "Cruise",
		"firstnames" : [ "Tom", "Robert" ]
	},
	{
		"lastname" : "Ford",
		"firstnames" : [ ]
	}
]

With a standard map, this is done using a SimpleLoop function that iterates on the root array and a NestedContext with a SimpleLoop that iterates on the firstnames array. In this case, the map returns the following result:

[
  {
    "lastname": "Cruise",
    "firstname": "Tom"
  },
  {
    "lastname": "Cruise",
    "firstname": "Robert"
  },
  {
    "lastname": "Ford",
    "firstname": ""
  }
]

However, with a DSQL map, using a FROM clause followed by an UNNEST clause, the map returns the following result:

[
   {
      "lastname": "Cruise",
      "firstname": "Tom"
   },
   {
      "lastname": "Cruise",
      "firstname": "Robert"
   }
]

In the case of the DSQL map, the Ford last name is ignored because the corresponding firstnames array is empty, which means there was nothing to iterate on.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – please let us know!

Leave your feedback here