bag_unpack plugin

Applies to: ✅ Microsoft Fabric ✅ Azure Data Explorer

The bag_unpack plugin unpacks a single column of type dynamic, by treating each property bag top-level slot as a column. The plugin is invoked with the evaluate operator.

Syntax

T | evaluate bag_unpack( Column [, OutputColumnPrefix ] [, columnsConflict ] [, ignoredProperties ] ) [: OutputSchema]

Learn more about syntax conventions.

Parameters

Name	Type	Required	Description
T	string	✔️	The tabular input whose column Column is to be unpacked.
Column	dynamic	✔️	The column of T to unpack.
OutputColumnPrefix	string		A common prefix to add to all columns produced by the plugin.
columnsConflict	string		The direction for column conflict resolution. Valid values: error - Query produces an error (default) replace_source - Source column is replaced keep_source - Source column is kept
ignoredProperties	dynamic		An optional set of bag properties to be ignored. }
OutputSchema			The names and types for the expected columns of the bag_unpack plugin output. Specifying the expected schema optimizes query execution by not having to first run the actual query to explore the schema. For syntax information, see Output schema syntax.

Output schema syntax

( ColumnName : ColumnType [, ...] )

To add all columns of the input table to the plugin output, use a wildcard * as the first parameter, as follows:

( * , ColumnName : ColumnType [, ...] )

Returns

The bag_unpack plugin returns a table with as many records as its tabular input (T). The schema of the table is the same as the schema of its tabular input with the following modifications:

• The specified input column (Column) is removed.
• The schema is extended with as many columns as there are distinct slots in the top-level property bag values of T. The name of each column corresponds to the name of each slot, optionally prefixed by OutputColumnPrefix. Its type is either the type of the slot, if all values of the same slot have the same type, or dynamic, if the values differ in type.

Note

• If the OutputSchema isn't specified, the plugin's output schema varies according to the input data values. Therefore, multiple executions of the plugin using different data inputs, may produce different output schema.

• The input data to the plugin must be such that the output schema follows all the rules for a tabular schema. In particular:

  • An output column name can't be the same as an existing column in the tabular input T, unless it's the column to be unpacked (Column), since that will produce two columns with the same name.

  • All slot names, when prefixed by OutputColumnPrefix, must be valid entity names and follow the identifier naming rules.

• Null values are ignored.

Examples

Expand a bag

Run the query

datatable(d:dynamic)
[
    dynamic({"Name": "John", "Age":20}),
    dynamic({"Name": "Dave", "Age":40}),
    dynamic({"Name": "Jasmine", "Age":30}),
]
| evaluate bag_unpack(d)

Output

Age	Name
20	John
40	Dave
30	Jasmine

Expand a bag with OutputColumnPrefix

Expand a bag and use the OutputColumnPrefix option to produce column names that begin with the prefix 'Property_'.

Run the query

datatable(d:dynamic)
[
    dynamic({"Name": "John", "Age":20}),
    dynamic({"Name": "Dave", "Age":40}),
    dynamic({"Name": "Jasmine", "Age":30}),
]
| evaluate bag_unpack(d, 'Property_')

Output

Property_Age	Property_Name
20	John
40	Dave
30	Jasmine

Expand a bag with columnsConflict

Expand a bag and use the columnsConflict option to resolve conflicts between existing columns and columns produced by the bag_unpack() operator.

Run the query

datatable(Name:string, d:dynamic)
[
    'Old_name', dynamic({"Name": "John", "Age":20}),
    'Old_name', dynamic({"Name": "Dave", "Age":40}),
    'Old_name', dynamic({"Name": "Jasmine", "Age":30}),
]
| evaluate bag_unpack(d, columnsConflict='replace_source') // Use new name

Output

Age	Name
20	John
40	Dave
30	Jasmine

Run the query

datatable(Name:string, d:dynamic)
[
    'Old_name', dynamic({"Name": "John", "Age":20}),
    'Old_name', dynamic({"Name": "Dave", "Age":40}),
    'Old_name', dynamic({"Name": "Jasmine", "Age":30}),
]
| evaluate bag_unpack(d, columnsConflict='keep_source') // Keep old name

Output

Age	Name
20	Old_name
40	Old_name
30	Old_name

Expand a bag with ignoredProperties

Expand a bag and use the ignoredProperties option to ignore certain properties in the property bag.

Run the query

datatable(d:dynamic)
[
    dynamic({"Name": "John", "Age":20, "Address": "Address-1" }),
    dynamic({"Name": "Dave", "Age":40, "Address": "Address-2"}),
    dynamic({"Name": "Jasmine", "Age":30, "Address": "Address-3"}),
]
// Ignore 'Age' and 'Address' properties
| evaluate bag_unpack(d, ignoredProperties=dynamic(['Address', 'Age']))

Output

Name
John
Dave
Jasmine

Expand a bag with a query-defined OutputSchema

Expand a bag and use the OutputSchema option to allow various optimizations to be evaluated before running the actual query.

Run the query

datatable(d:dynamic)
[
    dynamic({"Name": "John", "Age":20}),
    dynamic({"Name": "Dave", "Age":40}),
    dynamic({"Name": "Jasmine", "Age":30}),
]
| evaluate bag_unpack(d) : (Name:string, Age:long)

Output

Name	Age
John	20
Dave	40
Jasmine	30

Expand a bag and use the OutputSchema option to allow various optimizations to be evaluated before running the actual query. Use a wildcard * to return all columns of the input table.

Run the query

datatable(d:dynamic, Description: string)
[
    dynamic({"Name": "John", "Age":20}), "Student",
    dynamic({"Name": "Dave", "Age":40}), "Teacher",
    dynamic({"Name": "Jasmine", "Age":30}), "Student",
]
| evaluate bag_unpack(d) : (*, Name:string, Age:long)

Output

Description	Name	Age
Student	John	20
Teacher	Dave	40
Student	Jasmine	30