Parse and update JSON data - Simple Log Service - Alibaba Cloud Documentation Center

Scenario 1: Expand and extract a JSON object

Use the parse-json, json_extract_scalar, or json_extract functions to expand and extract a JSON object from a log.

Example 1: Expand the first level of JSON

This example expands the first-level key-value pairs in the data field.

Raw log

data: {"k1": "v1", "k2": {"k3": "v3", "k4": "v4"}}

SPL statement
```
* | parse-json data
```

Output log

data: {"k1": "v1", "k2": {"k3": "v3", "k4": "v4"}}
k1: v1
k2: {"k3": "v3", "k4": "v4"}

Example 2: Fully expand a JSON object

This example fully expands all key-value pairs in the data field.

Raw log

data: {"k1": "v1", "k2": {"k3": "v3", "k4": "v4"}}

SPL statement

* | parse-json data 
  | extend 
      k3 = json_extract_scalar(k2, '$.k3'),
      k4 = json_extract_scalar(k2, '$.k4')
  | project k1, k3, k4,data

Output log

data:{"k1": "v1", "k2": {"k3": "v3", "k4": "v4"}}
k1:v1
k3:v3
k4:v4

Example 3: Extract values by specifying keys

Raw log

data: {
	"foo": {
    	"bar": "baz"
    },
    "peoples": [{
        "name": "xh",
        "sex": "girl"
    }, {
        "name": "xm",
        "sex": "boy"
    }]
}

SPL statement

* | extend 
    name = json_extract_scalar(data, '$.peoples[0].name'),
    bar = json_extract_scalar(data, '$.foo.bar'),
    foo_obj = json_extract(data, '$.foo')

Output log

data:{"foo": {"bar": "baz"}, "peoples": [{"name": "xh", "sex": "girl"}, {"name": "xm", "sex": "boy"}]}
foo:{"bar": "baz"}
bar:baz
name:xh
names:["xh", "xm"]

Scenario 2: Extract values from a JSON object

Use the json_extract_scalar function to extract values from a JSON object in a log.

Example 1: JSON object contains the target field

This example extracts the value of the k1 key from a JSON object and creates a new field named key1.

Raw log
```
data: {"k1":"v1","k2":"v2"}
```

SPL statement

* | extend key1 = json_extract_scalar(data, '$.k1')

Output log
```
data:{"k1": "v1", "k2": "v2"}
key1:v1
```

Example 2: JSON object lacks the target field

This example shows how to extract a value from a JSON object. If the specified key does not exist, a new field key3 is created with a default value.

Raw log
```
data: {"k1":"v1","k2":"v2"}
```

SPL statement

* | extend key3 = coalesce(json_extract_scalar(data, '$.k3'), 'default')

Output log

data:{"k1": "v1", "k2": "v2"}
key3:default

Scenario 3: Update values in a JSON object

Use the replace function to update values in a JSON object.

This example modifies the value of the k1 key in a JSON object.

Raw log
```
data: {"k1":"v1","k2":"v2"}
```

SPL statement

* | extend data = replace(data, '"k1":"v1"', '"k1":"new_k1"')

Output log
```
data:{"k1": "new_k1", "k2": "v2"}
```

Scenario 4: Parse a value as a JSON object

Use the json_parse function to parse a string as a JSON object.

This example converts a string in the data field into a JSON object.

Raw log

data: "pre{ \"k1\": \"v1\", \"k2\": \"v2\"}"

SPL statement

* | extend json_object = replace(data, 'pre', '')
| extend json_object=json_parse(json_object)

Output log

data:pre{ "k1": "v1", "k2": "v2"}
json_object:{"k1": "v1", "k2": "v2"}