Master JSON Log Analysis in Alibaba Cloud SLS: Flatten, Index & AI Query

JSON is a popular log format because of its flexibility and readability, but large volumes of JSON logs pose challenges for fast analysis. This article presents a systematic approach to handling and analyzing JSON logs in Alibaba Cloud Log Service (SLS), covering data preprocessing, index configuration, advanced JSON functions, and AI‑assisted query generation.

1. Data Preprocessing – Flattening JSON at the Source

For relatively fixed‑structure JSON logs, the best practice is to flatten the nested fields into independent columns before the data is stored. Benefits include faster query performance (no runtime parsing) and lower storage cost (removing redundant braces, quotes, and commas).

Improved query speed by operating on flat fields directly.

Reduced storage overhead by eliminating JSON syntax noise.

SLS offers three ways to preprocess data:

Method 1: Process During Collection (Logtail Plugin)

If you use Logtail for log collection, enable the built‑in JSON plugin. The plugin parses JSON objects and expands them into separate fields at ingestion time. For logs that contain only a JSON string in a specific field, you can combine SPL statements to handle that field.

Method 2: Process at Write Time (Ingestion Processor)

When logs come from multiple sources (Logtail, API, SDK, etc.) or you cannot control the collector configuration, configure an ingestion processor on the Logstore. All incoming data passes through the processor before being persisted, allowing JSON flattening centrally.

Method 3: Post‑Write Processing (Data Processing Task)

If JSON logs are already stored, you can create a data processing task that reads from the source Logstore, applies SPL transformations, and writes the structured result to a new Logstore. This is useful for cleaning historical data.

Regardless of the method, SPL is the core tool for JSON manipulation, enabling flattening, extraction, and transformation.

2. Index Configuration – Balancing Structure and Query Performance

While flattening is ideal, sometimes you need to retain the original JSON structure to preserve hierarchical relationships. You can create a JSON‑type index on the whole field and add sub‑field indexes for frequently accessed leaf nodes, e.g., Payload.Status. This keeps the full JSON while allowing fast queries on hot paths.

If you have many fields, enable the "auto‑index all text fields in JSON" option, which automatically indexes every textual sub‑node. For example, even without an explicit index on Method, you can query Payload.Method directly.

3. JSON Functions – The Swiss‑Army Knife for Deep Analysis

SLS provides a rich set of JSON functions. Two fundamental functions are: json_extract(json, json_path): Returns a JSON object or array. Use when you need to manipulate the JSON structure itself (e.g., compute array length). json_extract_scalar(json, json_path): Returns a scalar value (string, number, boolean) as varchar. This is the most common function for extracting field values for analysis.

When extracting numeric values for calculations, cast the result to the appropriate type:

cast(json_extract_scalar(Payload, '$.Latency') as bigint) as latency

SLS also offers type‑specific extraction functions that avoid explicit casts: json_extract_long(json, json_path) – extracts as 64‑bit integer. json_extract_double(json, json_path) – extracts as double. json_extract_bool(json, json_path) – extracts as boolean.

JSON path syntax follows the pattern $.a.b, where $ denotes the root. If a key contains a dot, use bracket notation with double quotes, e.g., $."user.agent". Array elements are accessed with zero‑based indices, e.g., $.Params[0].value.

Analyzing JSON Arrays with unnest

When a log entry contains a JSON array (e.g., a list of key‑value pairs), you often need to explode the array into separate rows for aggregation. The unnest function does exactly that.

* | select json_extract_scalar(Payload, '$.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

This query extracts the Params array, casts it to array(json), expands each element with unnest, and then aggregates values by key.

4. SQL Copilot – AI‑Powered Query Generation

Writing complex SPL queries manually can be time‑consuming and error‑prone. SLS’s built‑in SQL Copilot lets you describe the analysis goal in natural language (e.g., "expand the Params array in Payload and compute the average value per key"), and it automatically generates the corresponding SQL.

Use Copilot to quickly obtain a baseline query, then fine‑tune it for performance or specific business logic.

Conclusion and Recommendations

Prioritize data regularization: flatten JSON at collection, ingestion, or via a processing task to achieve high performance and low cost.

Leverage indexes: create sub‑field indexes for hot paths or enable automatic full‑text indexing for JSON fields.

Master core functions: json_extract, json_extract_scalar, type‑specific extractors, and unnest are essential for flexible, real‑time analysis.

Embrace AI: use SQL Copilot to turn natural‑language intents into accurate queries, reducing development effort.

By applying these techniques, you can transform massive JSON log streams into actionable insights that drive business decisions.