arraymerge ↗

Use the arraymerge() function to flatten an input array containing JSON strings representing arrays into a single, merged XQL array.

Syntax

arraymerge (<field>)

Parameters

Name Type Required Description
field array Yes A single array field whose elements are JSON strings, where each JSON string represents an array (for example, ["value1", "value2"]).

Returns

The arraymerge() function returns a new, flattened XQL-native array containing all elements from the inner JSON-string-represented arrays.

Usage notes

  • The function strictly requires an array where each element is a valid JSON string that represents an array.
  • The function flattens the structure, taking an array of arrays (represented as JSON strings) and reducing it to a single-dimensional array.
  • This function is commonly used in conjunction with arraymap() when the internal function of arraymap() produces JSON strings of arrays.

Examples

Example 1: Merging artificially constructed arrays (literal JSON strings)

Goal: Demonstrate the core functionality by creating an array where each element is a JSON string representing an array, and then flattening them into a single array.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter json_array_str_part1 = to_json_string(arraycreate("tag1", "tag2")) // Creates JSON string '["tag1", "tag2"]' 
| alter json_array_str_part2 = to_json_string(arraycreate("valueA", "valueB")) // Creates JSON string '["valueA", "valueB"]' 
| alter input_array_for_merge = arraycreate(json_array_str_part1, json_array_str_part2) // Creates an array of these JSON strings: ['["tag1", "tag2"]', '["valueA", "valueB"]'] 
| alter merged_result = arraymerge(input_array_for_merge) 
| fields event_id, merged_result 
| limit 2

Explanation: The query first creates two string representations of arrays (json_array_str_part1 and json_array_str_part2) using arraycreate() and to_json_string(). The query then combines these into input_array_for_merge. Finally, arraymerge() extracts the elements from within each JSON string in the input array and combines them into a single, flattened merged_result array.

Output:

EVENT_ID MERGED_RESULT
101 ["tag1", "tag2", "valueA", "valueB"]
102 ["tag1", "tag2", "valueA", "valueB"]

Example 2: Merging scalar values extracted and wrapped into arrays via arraymap()

Goal: Demonstrate how to process an array of JSON objects, extract specific scalar values, wrap them into new conceptual arrays, convert them to JSON strings, and finally flatten the result using arraymerge().

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter mapped_json_arrays_as_strings = arraymap( 
    array_of_json_objects, 
    to_json_string( // Converts the dynamically created array into a JSON string 
        arraycreate( // Creates a new temporary array from extracted scalars 
            to_string(coalesce(json_extract_scalar(to_json_string("@element"), "$.action"), json_extract_scalar(to_json_string("@element"), "$.event"))), // Extracts 'action' or 'event' 
            to_string(coalesce(json_extract_scalar(to_json_string("@element"), "$.file"), json_extract_scalar(to_json_string("@element"), "$.path"), json_extract_scalar(to_json_string("@element"), "$.conn_type"))) // Extracts 'file', 'path', or 'conn_type' 
        ) 
    ) 
) 
| alter flattened_array = arraymerge(mapped_json_arrays_as_strings) 
| fields event_id, array_of_json_objects, flattened_array 
| limit 4

Explanation: arraymap() iterates over each JSON object in array_of_json_objects. For each element, it extracts specific scalar values (like action, event, file, path) using json_extract_scalar() and wraps them into a temporary array using arraycreate(). to_json_string() converts this temporary array into a JSON string. The result of arraymap() is an array of these JSON strings. Finally, arraymerge() flattens all the inner elements from these strings into a single flattened_array.

Output:

EVENT_ID ARRAY_OF_JSON_OBJECTS FLATTENED_ARRAY
101 [{"action": "read", "file": "doc1.txt"}, {"action": "write", "file": "report.log"}] ["read", "doc1.txt", "write", "report.log"]
102 [{"event": "file_open", "path": "/etc/passwd"}] ["file_open", "/etc/passwd"]
103 [{"conn_type": "outbound", "bytes": 1024}, {"conn_type": "inbound", "bytes": 512}] ["outbound", "inbound"]
104 [] []

Example 2: Merge IP addresses extracted from a nested map

Goal: Create a single consolidated array containing all IPv4 addresses found within the agent_interface_map field. This query extracts the "ipv4" element from each object in the map and merges them into a flattened array.

XQL Code:

dataset = sample_xql_raw
| alter a = arraymerge(arraymap(agent_interface_map, to_json_string(json_extract_array(to_json_string("@element"), "$.ipv4"))))

Explanation:

  1. The query processes the agent_interface_map, which is an array of objects.
  2. arraymap() iterates through each element (@element) in the array.
  3. to_json_string() converts the element to a JSON string so it can be parsed.
  4. json_extract_array(..., "$.ipv4") locates and extracts the IPv4 addresses associated with the "ipv4" key in each object.
  5. arraymerge() takes the resulting nested arrays and flattens them into a single, comprehensive array assigned to the field 'a'.

Output:

agent_interface_map a
[{"ipv4":["10.0.0.1"],"name":"eth0"},{"ipv4":["192.168.1.1"],"name":"wlan0"}] ["10.0.0.1", "192.168.1.1"]
[{"ipv4":["172.16.0.5", "172.16.0.6"],"name":"eth1"}] ["172.16.0.5", "172.16.0.6"]