IsObject() isArray()

Question

Hi, is there any IsArray() or IsObject() or IsBag() or typeof() function supported by Azure Data Explorer to check whether my Dynamic data is property bag or array, before applying mv-expand? My source sends semi-structured data to my application. First-scenario is such that the source data contains an array of data elements, each data element holding a pre-defined number of attributes. Second-scenario is it sends only single data element with (aforementioned) predefined set of attributes.

---

Example Scenario-1:

SourceData [3]

0 {5}

1 {5}

2 {5}

5 attributes being a1, a2, a3, a4, a5

Example Scenario-2:

SourceData {5}

---

If mv-expand is applied in scenario-1, source data gets flattened to 3 records with each record having 5 attributes, its the correct behaviour.

Whereas, mv-expand on the second scenario, results in source data getting flattened into 5 records, each with one of five attributes, which is wrong behaviour.

How to check the data type of the Source data, before applying mv-expand whether it is an array or property bag and and further check length in each case?

thanks

Answer 1

Hello @databricksuser-5173,

is there any IsArray() or IsObject() or IsBag() or typeof() function supported by Azure Data Explorer

Currently, these functions are not supported in Azure Data explorer.

How to check the data type of the Source data, before applying mv-expand whether it is an array or property bag and and further check length in each case?

You can achieve your requirement with the below alternative approach.

Here, first it checks whether the array_length(SourceData) != null or not and stores the Boolean value true or false a new column isArray. If it's not a null, then it means it is an array.

Similarly, it will check for bag_keys(SourceData) != null and if it is not a null, then it means it is a property bag. These values will be stored in another column isBag.

After that, it will give the length if it's an array or count of items if it's a property bag based on the isArray column values.

Here, before the mv-expand, it involves the usage of pack_array(SourceData), which converts property bags into an array and allows uniform handling in mv-expand.

let source_data_table = datatable(SourceData: dynamic) 

[

    dynamic([{"a1": 1, "a2": 2, "a3": 3, "a4": 4, "a5": 5}, {"a1": 6, "a2": 7, "a3": 8, "a4": 9, "a5": 10}, {"a1": 11, "a2": 12, "a3": 13, "a4": 14, "a5": 15}]), // Scenario 1

    dynamic({"a1": 1, "a2": 2, "a3": 3, "a4": 4, "a5": 5}),// Scenario 2

    dynamic({"a1": 20, "a2": 21, "a3": 22, "a4":23,"a5":24}) // Scenario 2

];

source_data_table

| extend isArray = isnotnull(array_length(SourceData))

| extend isBag = isnotnull(bag_keys(SourceData))

| extend length_or_count = iff(isArray, array_length(SourceData), array_length(bag_keys(SourceData)))

| extend FlattenedData = iff(isArray, SourceData, pack_array(SourceData))

| mv-expand FlattenedData

Sample Result for the above query:

If there is a need, you can expand the object data into the individual columns as per your requirement.

Hope this helps.

If the answer is helpful, please click Accept Answer and kindly upvote it. If you have any further questions about this answer, please click Comment.