Searching Logs with SQL: Inside Ju Jing OBSERVE's Search Engine

Log search is the first stop in any troubleshooting session. Most log platforms force you to choose between keyword matching and a proprietary query DSL you have to learn from scratch. Ju Jing OBSERVE's search engine reuses SQL directly, so ops and developers skip the new syntax entirely. This post covers field extraction, query patterns, and the performance rules that matter.

Why SQL instead of a custom DSL

SQL is the lingua franca of engineering. DBAs write it, backend engineers write it, and most ops people can read it. A custom DSL has real costs: the syntax doesn't transfer, documentation is thin, and every new teammate starts from zero. SQL has decades of tutorials, examples, and Stack Overflow answers behind it — search "GROUP BY syntax" and you'll find an answer in seconds.

SQL also maps naturally onto the two operations log analysis needs most: filtering (WHERE) and aggregation (GROUP BY). Ninety percent of log troubleshooting is "filter down to the relevant records, then count the distribution by some dimension." SQL expresses that in a single statement. There's a hidden bonus too: results drop straight into BI or reporting tools without re-exporting.

Where fields come from: three automatic extraction modes

Writing SQL requires that logs be split into fields first. Ju Jing OBSERVE does three things automatically at ingest time:

1. JSON parsing. JSON logs are flattened into fields; nested objects are accessed with dot notation, like span.attributes.http.status_code.
2. Key-value parsing. key=value or key: value pairs are split into fields, common in Nginx and HAProxy access logs.
3. Regex groups. Write a regex in the collection config, and named groups become field names — ideal for fixed-format custom logs.

The three modes stack: the same log line can run its JSON portion through JSON parsing and its prefix through a regex, with no conflict. For example, given 2026-08-31 10:00:01 order-service ERROR [trace=abc123] payment timeout, configure the regex \[trace=(?P<trace_id>\w+)\] and you can search WHERE trace_id = 'abc123'. Extraction happens at write time, so search never re-parses — one reason queries return in seconds.

Common queries: from field filters to aggregation

With fields in place, you can write queries. Take an Nginx access log:

{"time":"2026-08-31T10:00:00Z","status":502,"upstream":"10.0.3.21:8080","latency_ms":1200}

You can query it directly:

SELECT count(*), upstream
FROM nginx_access
WHERE status = 502
  AND time > now() - interval '1 hour'
GROUP BY upstream
ORDER BY count(*) DESC
LIMIT 20

This answers one concrete question: "In the last hour, which upstream instances returned 502, and how many times each?" status and upstream are auto-extracted fields — no schema to define up front, no field mapping to maintain.

Full-text search is still there: WHERE message LIKE '%timeout%' or WHERE message CONTAINS 'connection reset'. Aggregates cover count, avg, max, min, and percentile, so you can compute P95 and P99 latency directly:

SELECT upstream, percentile(latency_ms, 99) AS p99
FROM nginx_access
WHERE time > now() - interval '15 minute'
GROUP BY upstream

Indexing and performance: don't make full scans a habit

Performance is the easiest trap to fall into with SQL search. Three rules:

1. Always bound the time range. Logs are sharded by time; WHERE time > ... lets the engine scan only the relevant shards. A query without a time range is silently capped at the last 24 hours, with a hint shown.
2. Avoid GROUP BY on high-cardinality fields. Grouping by request_id or trace_id — fields where every value appears once — produces millions of groups, slow and useless. Ask first whether the dimension has aggregation value.
3. Put regex and full-text after equality filters. The engine runs equality filters like status = 502 first to shrink the candidate set before doing expensive regex matching. The optimizer reorders automatically, but writing the regex inside WHERE is still cheaper than applying it later.

Practical takeaways

Save common troubleshooting queries as shared templates. Things like "top upstreams by error rate in the last 15 minutes," "all logs tied to a given trace," or "slow-request distribution for a service" — once saved, the on-call engineer opens them with one click instead of writing SQL at 3 a.m. Give each template a clear name and a one-line "when to use it" note, so a new teammate can tell which one to click just by scanning the list.

Search is only the first step. From any result you can drill down into the trace or create an alert from the query itself. Setting SELECT count(*) WHERE status >= 500 to run every five minutes and notify above a threshold is like installing a standing probe on your error rate — and that's what moves you from reactive firefighting to catching problems before users do.