---
title: "To json (jsonparse)"
canonical: "https://docs.devo.com/space/latest/95198015/To%20json%20(jsonparse)"
format: markdown
---
> Macro (toc)
## 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.
## Description
Converts a *string* data type into *json* data type, which is required as an argument for the [Jq evaluation (jqeval)](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95198042) 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** |
| --- | --- | --- |
| **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.*
> Macro (excerpt-include)
### 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)](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95198042) 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)](https://devodocs.atlassian.net/wiki/spaces/latest/pages/95198042) 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
```
## 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.
- 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.
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.
### Unwanted quotes
To get rid of unwanted quotation marks in a string JSON value, use `str()`.
### 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.](https://devodocs.atlassian.net/wiki/spaces/latest/pages/203423786)”
4. Select “Nodes.”
5. Select “Extract” twice.
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`
```