The dynamic data type - Azure Data Explorer (2023)

  • Article
  • 7 minutes to read

The dynamic scalar data type is special in that it can take on any value of other scalar data types from the list below, as well as arrays and property bags. Specifically, a dynamic value can be:

  • Null.
  • A value of any of the primitive scalar data types:bool, datetime, guid, int, long, real, string, and timespan.
  • 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.


  • Values of type dynamic are limited to 1MB (2^20), uncompressed.
  • Although the dynamic type appears JSON-like, it can hold values that the JSONmodel does not 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 representare serialized into string values. Conversely, Kusto will parse stringsas strongly-typed values if they can be parsed as such.This applies for datetime, real, long, and guid types.For more about the JSON object model, see
  • Kusto doesn't attempt to preserve the order of name-to-value mappings ina property bag, and so you can't assume the order to be preserved. It's entirelypossible for two property bags with the same set of mappings to yield differentresults when they are represented as string values, for example.

Dynamic literals

A literal of type dynamic looks like this:

(Video) Azure Data Explorer (ADX) Overview

dynamic( Value )

Value can be:

  • null, in which case the literal represents the null dynamic value:dynamic(null).
  • Another scalar data type literal, in which case the literal represents thedynamic literal of the "inner" type. For example, dynamic(4) isa dynamic value holding the value 4 of the long scalar data type.
  • An array of dynamic or other literals: [ ListOfValues ]. For example,dynamic([1, 2, "hello"]) is a dynamic array of three elements, two long valuesand one string value.
  • A property bag: { Name = Value ... }. For example, dynamic({"a":1, "b":{"a":2}})is a property bag with two slots, a, and b, with the second slot beinganother 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 dynamicvalue, 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 samevalue as o in the example above.


(Video) An overview of Azure Data Explorer (ADX) | Azure Friday

Unlike JavaScript, JSON mandates 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 usinga single-quote (') character.

The following example shows how you can define a table that holds a dynamic column (as well asa datetime column) and then ingest into it a single record. it also demonstrates how youcan 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!""}"]


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

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.


(Video) Big Data Analytics with Azure Data Explorer

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:

ExpressionAccessor expression typeMeaningComments
dict[col]Entity name (column)Subscripts a dictionary using the values of the column col as the keyColumn must be of type string
arr[index]Entity index (column)Subscripts an array using the values of the column index as the indexColumn must be of type integer or boolean
arr[-index]Entity index (column)Retrieves the 'index'-th value from the end of the arrayColumn must be of type integer or boolean
arr[(-1)]Entity indexRetrieves the last value in the array
arr[toint(indexAsString)]Function callCasts 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 keyEntity names that are identical to some query language keywords must be quoted
dict.['where'] or dict['where']ConstantSubscripts a dictionary using where string as the key

Performance tip: Prefer to use 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 gettypefunction to discover the actual underlying type of the value, and anyof 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.

Yparse_json('{"a1":100, "a b c":"2015-01-01"}')dictionary
Y["a b c"]parse_json("2015-01-01")dynamic
todate(Y["a b c"])datetime(2015-01-01)datetime

Cast functions are:

(Video) Azure Data Explorer

  • 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 name/value pairs.
  • 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 dynamicarrays 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 functionUsage with dynamic data types
value in arrayTrue if there's an element of array that == value
where City in ('London', 'Paris', 'Rome')
value !in arrayTrue 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 listColumnReplicates 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.


1. Azure Data Explorer ADX Overview | Data Services Community
(Data Services Community)
2. Azure Data Factory Parametrization Tutorial
(Adam Marczak - Azure for Everyone)
3. Data Types and How to Create Table by using Kusto Query | Kusto Query Language Tutorial (KQL)
4. Kusto Query Language (KQL) Overview
(John Savill's Technical Training)
5. Database Management Commands For Azure Data Explorer by using Kusto Query | KQL Tutorial
6. What is Azure Data Lake and When to Use It
(CBT Nuggets)
Top Articles
Latest Posts
Article information

Author: Margart Wisoky

Last Updated: 02/10/2023

Views: 6138

Rating: 4.8 / 5 (58 voted)

Reviews: 89% of readers found this page helpful

Author information

Name: Margart Wisoky

Birthday: 1993-05-13

Address: 2113 Abernathy Knoll, New Tamerafurt, CT 66893-2169

Phone: +25815234346805

Job: Central Developer

Hobby: Machining, Pottery, Rafting, Cosplaying, Jogging, Taekwondo, Scouting

Introduction: My name is Margart Wisoky, I am a gorgeous, shiny, successful, beautiful, adventurous, excited, pleasant person who loves writing and wants to share my knowledge and understanding with you.