/
To json (jsonparse)
Document toolboxDocument toolbox

To json (jsonparse)

Owned by Former user (Deleted)
Last updated: Mar 18, 2025 by Laszlo FrazerVersion comment
4 min read
[ 1 Purpose ] [ 2 Description ] [ 3 How does it work in the search window? ] [ 4 How does it work in LINQ? ] [ 5 Troubleshooting ]

Purpose

If a field contains a JSON object, such as

{"key":"value", "array":[1,2,3]}

then use jsonparse to get data out of the field.

image-20250304-161504.png

Description

Converts a string data type into json data type, which is required as an argument for the Jq evaluation (jqeval) operation.

How does it work in the search window?

Select Create field in the search window toolbar, then select the To json operation. You need to specify one argument:

Argument

Data type

Description

Argument

Data type

Description

String to convert mandatory

string

The full sample with json syntax. You can manually enter the string or select a field with json data type. 

Key

string

Optionally, use a key in [ ]to extract a specific value.

The data type of the values in the new field is json.

Example

In this example, in the siem.logtrust.web.activity table, we will manually specify a string to create a json field, which can be later used as an argument in the Jq evaluation (jqeval) operation.

  • Click the Create field button on the toolbar, select the To json (jsonparse) operation and give the new field a name.

  • Add the necessary argument and configure it as shown in the picture below:

    • string to convert → manually introduced →  {"name":"John","birth":"1986-12-14","city":"New York"}

  • Click Create field and a new field will be created with the specified string in json data type.

Now you have your new field ready to use as an argument in the Jq evaluation (jqeval) operation.

How does it work in LINQ?

Example

from siem.logtrust.collector.counter select //create a string containing JSON "{\"key\":\"value\", \"array\":[1,2,3]}" as string, //convert the string to type JSON jsonparse(string) as json, //convert only one value from a key-value pair to type JSON jsonparse(string)["key"] as value_json, //convert only one element from an array to type JSON jqeval(jqcompile(".[0]"),jsonparse(string)["array"]) as array_element_json,//indexed from zero //convert a string in JSON to data type string str(jsonparse(string)["key"]) as value_string, //convert any value in JSON to data type string stringify(jsonparse(string)["array"]) as array_string
image-20250318-152652.png

Troubleshooting

Null values

  • Invalid JSON
    If the JSON object is not complete, then the returned value will be null. This sometimes occurs if the event has been truncated by the data source.

    from siem.logtrust.collector.counter select "{\"key\":\"value\"" as string, jsonparse(string) as json

  • The requested key does not exist
    In the expression jsonparse(string)["foo"], if the JSON object does not have the key “foo” in a particular log, the result will be null. Verify the key is spelled correctly. It is very common for a key to only appear in the log occasionally, meaning the result of the expression will almost always be null.

  • Unescaped characters
    Special characters in JSON must be escaped with a \. In this example, the quotation marks inside the JSON value should have been escaped. Since they were not escaped, isnull(jsonparse(…)) is true.

    image-20250304-203131.png

    If data is being sent to Devo, the quote inside the JSON object should be \" to comply with the JSON standard. If the JSON object is part of a query, the quote should be \\\".

  • str(…) for non-strings
    If the JSON object contains a value that is not a string, str(…) will return null. To convert the object to a string, use stringify(...) instead.

image-20250304-203639.png

Unwanted quotes

To get rid of unwanted quotation marks in a string JSON value, use str().

image-20250304-210107.png

Arrays

Devo computes the columns in a query result using the query and not the data. If the data contains a fixed number of elements, those elements may become fields. If the data contains a variable number of elements, those variable elements may become an array or a JSON array using one field.

JSON arrays are indexed so that the first item in the array is called 0. There are several different syntax options for picking out an element of an array.

from siem.logtrust.collector.counter select "{\"key\":[1,2,3,4,5]}" as string, jsonparse(string)["key"] as json_array, jsonparse(string)["key"][1] as second_element, json_array[2] as third_element, at(json_array,3) as fourth_element, jqeval(jqcompile(".[4]"),json_array) as fifth_element

Use the length key to check how many elements are in an array.

from siem.logtrust.collector.counter select "[1,2,3,4,5]" as string, jsonparse(string)["length"]

To quickly add all the elements of a JSON array to a query:

  1. Find a cell containing the JSON data type.

  2. Open the context menu.

  3. Select “Pretty print.”

  4. Select “Nodes.”

  5. Select “Extract” twice.

    image-20250305-020820.png

    As an example, this will change the query to

from siem.logtrust.collector.counter select "[1,2,3,4,5]" as string, jsonparse(string) as json, int(jqeval(jqcompile(".[0]"), json)) as `0`, int(jqeval(jqcompile(".[1]"), json)) as `1`, int(jqeval(jqcompile(".[2]"), json)) as `2`, int(jqeval(jqcompile(".[3]"), json)) as `3`, int(jqeval(jqcompile(".[4]"), json)) as `4`

Related content