Mastering JSON Log Analysis in Alibaba Cloud SLS: Best Practices & Tips

Data Preprocessing

Flattening JSON logs before storage improves query performance and reduces storage cost by expanding nested fields into independent columns.

SLS offers three ways to preprocess data:

Collect-time processing (Logtail) : Use the built‑in JSON plugin to parse and flatten JSON objects during collection, or apply SPL statements to specific fields.

Write-time processing (Ingestion Processor) : Configure a processor on the Logstore to uniformly flatten JSON for all incoming data, regardless of source.

Post‑write processing (Data Processing) : Use data processing tasks to read existing logs, apply SPL transformations, and write the structured results to a new Logstore for historical data.

Index Configuration

When retaining the original JSON structure, create JSON‑type indexes on frequently queried leaf nodes (e.g., Payload.Status) while keeping the full JSON field indexed for flexible queries.

Optionally enable automatic indexing of all text fields within JSON to allow keyword searches on any sub‑field.

JSON Functions

SLS provides a suite of JSON functions for deep analysis: json_extract(json, json_path): Returns a JSON object or array. json_extract_scalar(json, json_path): Returns a scalar value (VARCHAR).

Type‑specific extractors such as json_extract_long, json_extract_double, and json_extract_bool avoid extra CAST operations. json_array_length(json) and json_path let you work with JSON arrays and nested fields.

* | select json_extract_scalar(Payload, '$.Method') as method, avg(cast(json_extract_scalar(Payload, '$.Latency') as bigint)) as latency group by method

JSON Array Analysis with UNNEST

The unnest function expands a JSON array into separate rows, enabling aggregation on each element.

* | select json_extract_scalar(kv, '$.key') as key, avg(json_extract_long(kv, '$.value')) as value FROM log, unnest(cast(json_extract(Payload, '$.Params') as array(json))) as t(kv) group by key

SQL Copilot (AI‑Assisted Queries)

SQL Copilot generates SQL statements from natural‑language descriptions, allowing you to focus on analysis goals rather than query syntax.

Summary & Recommendations

Prioritize data normalization at ingestion using Logtail plugins, ingestion processors, or data processing tasks for optimal performance and cost.

Leverage JSON indexes or automatic text indexing to accelerate frequent queries.

Master core JSON functions ( json_extract, json_extract_scalar, unnest) for flexible, real‑time analysis.

Use AI‑driven SQL Copilot to quickly generate and refine queries, reducing manual effort.