The dynamic data type

  • Article

Applies to: ✅ Azure Data Explorer ✅ Azure Monitor ✅ Microsoft Sentinel

The dynamic scalar data type can be any of the following values:

  • An array of dynamic values, holding zero or more values with zero-based indexing.
  • A property bag that maps unique string values to dynamic values. The property bag has zero or more such mappings (called "slots"), indexed by the unique string values. The slots are unordered.
  • A value of any of the primitive scalar data types: bool, datetime, guid, int, long, real, string, and timespan.
  • Null. For more information, see Null values.

Note

  • Values of type dynamic are limited to 1MB (2^20), uncompressed. If a cell value in a record exceeds 1MB, the value is dropped and ingestion succeeds. You can increase the MaxValueSize of the column by changing its encoding policy.
  • Although the dynamic type appears JSON-like, it can hold values that the JSON model doesn't represent because they don't exist in JSON (e.g. long, real, datetime, timespan, and guid). Therefore, in serializing dynamic values into a JSON representation, values that JSON can't represent are serialized into string values. Conversely, Kusto will parse strings as strongly-typed values if they can be parsed as such. This applies to datetime, real, long, and guid types. For more information on the JSON object model, see json.org.
  • Kusto doesn't attempt to preserve the order of name-to-value mappings in a property bag, and so you can't assume the order to be preserved. It's entirely possible for two property bags with the same set of mappings to yield different results when they are represented as string values, for example.

Dynamic literals

To specify a dynamic literal, use one of the following syntax options:

Syntax Description Example
dynamic([value [, ...]]) An array of dynamic or other scalar literals. dynamic([1, 2, "hello"])
dynamic({key = value [, ...]}) A property bag, or object. The value for a key can be a nested property bag. dynamic({"a":1, "b":{"a":2}})
dynamic(value) A dynamic value holding the value of the inner scalar data type. dynamic(4)
dynamic(null) Represents the null value.

Learn more about syntax conventions.

Dynamic object accessors

To subscript a dictionary, use either the dot notation (dict.key) or the brackets notation (dict["key"]). When the subscript is a string constant, both options are equivalent.

Note

To use an expression as the subscript, use the brackets notation. When using arithmetic expressions, the expression inside the brackets must be wrapped in parentheses.

In the examples below dict and arr are columns of dynamic type:

Expression Accessor expression type Meaning Comments
dict[col] Entity name (column) Subscripts a dictionary using the values of the column col as the key Column must be of type string
arr[index] Entity index (column) Subscripts an array using the values of the column index as the index Column must be of type integer or boolean
arr[-index] Entity index (column) Retrieves the 'index'-th value from the end of the array Column must be of type integer or boolean
arr[(-1)] Entity index Retrieves the last value in the array
arr[toint(indexAsString)] Function call Casts the values of column indexAsString to int and use them to subscript an array
dict[['where']] Keyword used as entity name (column) Subscripts a dictionary using the values of column where as the key Entity names that are identical to some query language keywords must be quoted
dict.['where'] or dict['where'] Constant Subscripts a dictionary using where string as the key

Tip

We recommend using constant subscripts when possible.

Accessing a sub-object of a dynamic value yields another dynamic value, even if the sub-object has a different underlying type. Use the gettype function to discover the actual underlying type of the value, and any of the cast function listed below to cast it to the actual type.

Casting dynamic objects

After subscripting a dynamic object, you must cast the value to a simple type.

Expression Value Type
X parse_json('[100,101,102]') array
X[0] parse_json('100') dynamic
toint(X[1]) 101 int
Y parse_json('{"a1":100, "a b c":"2015-01-01"}') dictionary
Y.a1 parse_json('100') dynamic
Y["a b c"] parse_json("2015-01-01") dynamic
todate(Y["a b c"]) datetime(2015-01-01) datetime

Cast functions are:

  • tolong()
  • todouble()
  • todatetime()
  • totimespan()
  • tostring()
  • toguid()
  • parse_json()

Building dynamic objects

Several functions enable you to create new dynamic objects:

  • bag_pack() creates a property bag from name/value pairs.
  • pack_array() creates an array from list of values (can be list of columns, for each row it will create an array from the specified columns).
  • range() creates an array with an arithmetic series of numbers.
  • zip() pairs "parallel" values from two arrays into a single array.
  • repeat() creates an array with a repeated value.

Additionally, there are several aggregate functions which create dynamic arrays to hold aggregated values:

  • buildschema() returns the aggregate schema of multiple dynamic values.
  • make_bag() returns a property bag of dynamic values within the group.
  • make_bag_if() returns a property bag of dynamic values within the group (with a predicate).
  • make_list() returns an array holding all values, in sequence.
  • make_list_if() returns an array holding all values, in sequence (with a predicate).
  • make_list_with_nulls() returns an array holding all values, in sequence, including null values.
  • make_set() returns an array holding all unique values.
  • make_set_if() returns an array holding all unique values (with a predicate).

Operators and functions over dynamic types

For a complete list of scalar dynamic/array functions, see dynamic/array functions.

Operator or function Usage with dynamic data types
value in array True if there's an element of array that == value
where City in ('London', 'Paris', 'Rome')
value !in array True if there's no element of array that == value
array_length(array) Null if it isn't an array
bag_has_key(bag,key) Checks whether a dynamic bag column contains a given key.
bag_keys(bag) Enumerates all the root keys in a dynamic property-bag object.
bag_merge(bag1,...,bagN) Merges dynamic property-bags into a dynamic property-bag with all properties merged.
bag_set_key(bag,key,value) Sets a given key to a given value in a dynamic property-bag.
extract_json(path,object), extract_json(path,object) Use path to navigate into object.
parse_json(source) Turns a JSON string into a dynamic object.
range(from,to,step) An array of values.
mv-expand listColumn Replicates a row for each value in a list in a specified cell.
summarize buildschema(column) Infers the type schema from column content.
summarize make_bag(column) Merges the property bag (dictionary) values in the column into one property bag, without key duplication.
summarize make_bag_if(column,predicate) Merges the property bag (dictionary) values in the column into one property bag, without key duplication (with predicate).
summarize make_list(column) Flattens groups of rows and puts the values of the column in an array.
summarize make_list_if(column,predicate) Flattens groups of rows and puts the values of the column in an array (with predicate).
summarize make_list_with_nulls(column) Flattens groups of rows and puts the values of the column in an array, including null values.
summarize make_set(column) Flattens groups of rows and puts the values of the column in an array, without duplication.

Indexing for dynamic data

Every field is indexed during data ingestion. The scope of the index is a single data shard.

To index dynamic columns, the ingestion process enumerates all "atomic" elements within the dynamic value (property names, values, array elements) and forwards them to the index builder. Otherwise, dynamic fields have the same inverted term index as string fields.

Examples

Dynamic property bag

The following query creates a dynamic property bag.

print o=dynamic({"a":123, "b":"hello", "c":[1,2,3], "d":{}})
| extend a=o.a, b=o.b, c=o.c, d=o.d

For convenience, dynamic literals that appear in the query text itself may also include other Kusto literals with types: datetime, timespan, real, long, guid, bool, and dynamic. This extension over JSON isn't available when parsing strings (such as when using the parse_json function or when ingesting data), but it enables you to do the following:

print d=dynamic({"a": datetime(1970-05-11)})

To parse a string value that follows the JSON encoding rules into a dynamic value, use the parse_json function. For example:

  • parse_json('[43, 21, 65]') - an array of numbers
  • parse_json('{"name":"Alan", "age":21, "address":{"street":432,"postcode":"JLK32P"}}') - a dictionary
  • parse_json('21') - a single value of dynamic type containing a number
  • parse_json('"21"') - a single value of dynamic type containing a string
  • parse_json('{"a":123, "b":"hello", "c":[1,2,3], "d":{}}') - gives the same value as o in the example above.

Note

Unlike JavaScript, JSON requires the use of double-quote (") characters around strings and property-bag property names. Therefore, it is generally easier to quote a JSON-encoded string literal by using a single-quote (') character.

Ingest data into dynamic columns

The following example shows how you can define a table that holds a dynamic column (as well as a datetime column) and then ingest single record into it. It also demonstrates how you can encode JSON strings in CSV files.

// dynamic is just like any other type:
.create table Logs (Timestamp:datetime, Trace:dynamic)

// Everything between the "[" and "]" is parsed as a CSV line would be:
// 1. Since the JSON string includes double-quotes and commas (two characters
//    that have a special meaning in CSV), we must CSV-quote the entire second field.
// 2. CSV-quoting means adding double-quotes (") at the immediate beginning and end
//    of the field (no spaces allowed before the first double-quote or after the second
//    double-quote!)
// 3. CSV-quoting also means doubling-up every instance of a double-quotes within
//    the contents.

.ingest inline into table Logs
  [2015-01-01,"{""EventType"":""Demo"", ""EventValue"":""Double-quote love!""}"]

Output

Timestamp Trace
2015-01-01 00:00:00.0000000 {"EventType":"Demo","EventValue":"Double-quote love!"}

Related content