feat(tracedetail): read waterfall/flamegraph span attributes from the JSON column

Span rows hold their attributes either in the legacy maps or in the
`attributes` JSON column (dual-written rows in both), so the trace span
reads (GetTraceSpans, GetTraceSpansByIDs, GetFlamegraphSpans) suppress
the JSON document per row when any legacy map carries the row's
attributes: if(notEmpty(attributes_string) OR ..., CAST('{}', 'JSON'),
attributes). The result set carries one attribute home per span instead
of both — full waterfall/flamegraph load up to 10k/100k spans in
memory, and reading both homes during dual-write would double that.
Probing the maps (not the JSON column) is what tells dual-written rows
apart from json-only ones.

StorableSpan gains AttributesJSON; the merged bag keeps its flat
dotted-key shape (Attributes() flattens the JSON document first and
lays the maps over it, maps winning on collision — same precedence as
the querier list view). The flamegraph selectFields path builds that
bag at most once per span and resolves dotted names through it, which
subsumes AttributeValue(). FlattenJSONPaths moves to
telemetrystoretypes to be shared with the querier.

pkg/modules/tracedetail/impltracedetail/store.go

@@ -4,6 +4,7 @@ import (
 	"context"
 	"database/sql"
 	"fmt"
+	"strings"
 	"time"
 
 	sqlbuilder "github.com/huandu/go-sqlbuilder"
@@ -17,6 +18,26 @@ import (
 
 const colServiceName = `resource_string_service$$$$name` // $ gets escaped so $$$$ converts to $$.
 
+// attrAnyMapNonEmpty probes whether any legacy attribute map holds data for the row: pre-rollout
+// and dual-written rows populate the maps, json-only rows leave all three empty. Probing the
+// maps (not the JSON column) is what tells dual-written rows apart from json-only ones.
+const attrAnyMapNonEmpty = `notEmpty(attributes_string) OR notEmpty(attributes_number) OR notEmpty(attributes_bool)`
+
+// attrJSONIfMapsEmpty suppresses the `attributes` JSON document to an empty object when any
+// legacy map carries the row's attributes, so the result set carries one attribute home per
+// span instead of both (the Go merge flattens JSON first and lays the maps over it, so the
+// maps win on collision, matching the querier's list view).
+var attrJSONIfMapsEmpty = fmt.Sprintf("if(%s, CAST('{}', 'JSON'), attributes)", attrAnyMapNonEmpty)
+
+// spanAttributeHomeSelection is the SELECT fragment for the span attribute homes: the three
+// legacy maps plus the conditionally suppressed JSON column.
+var spanAttributeHomeSelection = strings.Join([]string{
+	"attributes_string",
+	"attributes_number",
+	"attributes_bool",
+	attrJSONIfMapsEmpty + " AS attributes",
+}, ", ")
+
 func buildFieldExpr(fieldKey telemetrytypes.TelemetryFieldKey) (string, error) {
 	switch fieldKey.FieldContext {
 	case telemetrytypes.FieldContextResource:
@@ -67,11 +88,15 @@ func (s *traceStore) GetTraceSummary(ctx context.Context, traceID string) (*span
 
 func (s *traceStore) GetTraceSpans(ctx context.Context, traceID string, summary *spantypes.TraceSummary) ([]spantypes.StorableSpan, error) {
 	// DISTINCT ON (span_id) is ClickHouse-specific syntax not supported by sqlbuilder
+	//
+	// %s carries the span-attribute home selection: rows hold their attributes either in the
+	// legacy maps or in the `attributes` JSON column (never only partially), so per row the
+	// empty home is suppressed to keep the result set at one home per span.
 	query := fmt.Sprintf(`
 		SELECT DISTINCT ON (span_id)
 			timestamp, duration_nano, span_id, has_error, kind,
 			resource_string_service$$name, name,
-			attributes_string, attributes_number, attributes_bool, resources_string,
+			%s, resources_string,
 			events, status_message, status_code_string, kind_string, parent_span_id,
 			flags, is_remote, trace_state, status_code,
 			db_name, db_operation, http_method, http_url, http_host,
@@ -79,7 +104,7 @@ func (s *traceStore) GetTraceSpans(ctx context.Context, traceID string, summary
 		FROM %s.%s
 		WHERE trace_id=? AND ts_bucket_start>=? AND ts_bucket_start<=?
 		ORDER BY timestamp ASC, name ASC`,
-		spantypes.TraceDB, spantypes.TraceTable,
+		spanAttributeHomeSelection, spantypes.TraceDB, spantypes.TraceTable,
 	)
 	var spanItems []spantypes.StorableSpan
 	err := s.telemetryStore.ClickhouseDB().Select(
@@ -127,7 +152,7 @@ func (s *traceStore) GetTraceSpansByIDs(ctx context.Context, traceID string, sta
 		"DISTINCT ON (span_id) timestamp",
 		"duration_nano", "span_id", "has_error", "kind",
 		colServiceName, "name",
-		"attributes_string", "attributes_number", "attributes_bool", "resources_string",
+		spanAttributeHomeSelection, "resources_string",
 		"events", "status_message", "status_code_string", "kind_string", "parent_span_id",
 		"flags", "is_remote", "trace_state", "status_code",
 		"db_name", "db_operation", "http_method", "http_url", "http_host",
@@ -168,6 +193,7 @@ func (s *traceStore) GetFlamegraphSpans(ctx context.Context, traceID string, sta
 		"any(attributes_string) AS attributes_string",
 		"any(attributes_number) AS attributes_number",
 		"any(attributes_bool) AS attributes_bool",
+		fmt.Sprintf("any(%s) AS attributes", attrJSONIfMapsEmpty),
 		"any(resources_string) AS resources_string",
 	)
 	sb.From(fmt.Sprintf("%s.%s", spantypes.TraceDB, spantypes.TraceTable))

pkg/modules/tracedetail/impltracedetail/store_test.go

@@ -91,9 +91,37 @@ func TestGetSpanCountByField(t *testing.T) {
 	}
 }
 
+// attrHomeSQL pins the per-row home-suppression fragment shared by the span reads: the legacy
+// maps are always selected and the JSON column is emptied when any map carries the row's
+// attributes, so dual-written rows resolve map-side (maps win on collision).
+const attrHomeSQL = `attributes_string, attributes_number, attributes_bool, ` +
+	`if\(notEmpty\(attributes_string\) OR notEmpty\(attributes_number\) OR notEmpty\(attributes_bool\), CAST\('\{\}', 'JSON'\), attributes\) AS attributes, resources_string`
+
+func TestGetTraceSpans(t *testing.T) {
+	s := newTestStore(sqlmock.QueryMatcherRegexp)
+	s.Mock().ExpectSelect(`(?s)SELECT\s+DISTINCT ON \(span_id\).*?` + attrHomeSQL + `.*?FROM signoz_traces\.distributed_signoz_index_v3`).
+		WillReturnRows(cmock.NewRows(nil, nil))
+	_, _ = s.Store().GetTraceSpans(context.Background(), testTraceID, testSummary)
+	assert.NoError(t, s.Mock().ExpectationsWereMet())
+}
+
+func TestGetTraceSpansByIDs(t *testing.T) {
+	s := newTestStore(sqlmock.QueryMatcherRegexp)
+	s.Mock().ExpectSelect(`SELECT DISTINCT ON \(span_id\) timestamp.*?` + attrHomeSQL + `.*?FROM signoz_traces\.distributed_signoz_index_v3 WHERE trace_id = \? AND span_id IN \(\?, \?\)`).
+		WillReturnRows(cmock.NewRows(nil, nil))
+	_, _ = s.Store().GetTraceSpansByIDs(context.Background(), testTraceID, testStart, testEnd, []string{"span-1", "span-2"})
+	assert.NoError(t, s.Mock().ExpectationsWereMet())
+}
+
 func TestGetFlamegraphSpans(t *testing.T) {
-	baseSQL := "SELECT span_id, any(parent_span_id) AS parent_span_id, any(timestamp) AS timestamp, any(duration_nano) AS duration_nano, any(has_error) AS has_error, any(name) AS name, any(events) AS events, any(attributes_string) AS attributes_string, any(attributes_number) AS attributes_number, any(attributes_bool) AS attributes_bool, any(resources_string) AS resources_string FROM signoz_traces.distributed_signoz_index_v3 WHERE trace_id = ? AND ts_bucket_start >= ? AND ts_bucket_start <= ? GROUP BY span_id ORDER BY timestamp ASC, name ASC"
-	withSpanIDsSQL := "SELECT span_id, any(parent_span_id) AS parent_span_id, any(timestamp) AS timestamp, any(duration_nano) AS duration_nano, any(has_error) AS has_error, any(name) AS name, any(events) AS events, any(attributes_string) AS attributes_string, any(attributes_number) AS attributes_number, any(attributes_bool) AS attributes_bool, any(resources_string) AS resources_string FROM signoz_traces.distributed_signoz_index_v3 WHERE trace_id = ? AND ts_bucket_start >= ? AND ts_bucket_start <= ? AND span_id IN (?, ?) GROUP BY span_id ORDER BY timestamp ASC, name ASC"
+	// The map columns are aggregated as-is and the JSON column is wrapped in per-row home
+	// suppression (emptied when any legacy map carries the row's attributes).
+	anyAttrSQL := "any(attributes_string) AS attributes_string, " +
+		"any(attributes_number) AS attributes_number, " +
+		"any(attributes_bool) AS attributes_bool, " +
+		"any(if(notEmpty(attributes_string) OR notEmpty(attributes_number) OR notEmpty(attributes_bool), CAST('{}', 'JSON'), attributes)) AS attributes"
+	baseSQL := "SELECT span_id, any(parent_span_id) AS parent_span_id, any(timestamp) AS timestamp, any(duration_nano) AS duration_nano, any(has_error) AS has_error, any(name) AS name, any(events) AS events, " + anyAttrSQL + ", any(resources_string) AS resources_string FROM signoz_traces.distributed_signoz_index_v3 WHERE trace_id = ? AND ts_bucket_start >= ? AND ts_bucket_start <= ? GROUP BY span_id ORDER BY timestamp ASC, name ASC"
+	withSpanIDsSQL := "SELECT span_id, any(parent_span_id) AS parent_span_id, any(timestamp) AS timestamp, any(duration_nano) AS duration_nano, any(has_error) AS has_error, any(name) AS name, any(events) AS events, " + anyAttrSQL + ", any(resources_string) AS resources_string FROM signoz_traces.distributed_signoz_index_v3 WHERE trace_id = ? AND ts_bucket_start >= ? AND ts_bucket_start <= ? AND span_id IN (?, ?) GROUP BY span_id ORDER BY timestamp ASC, name ASC"
 
 	tests := []struct {
 		name    string

pkg/querier/consume.go

@@ -566,23 +566,7 @@ func readAsRaw(rows driver.Rows, queryName string) (*qbtypes.RawData, error) {
 	}, nil
 }
 
-// flattenJSONPaths flattens a decoded JSON document into dotted keys, overwriting existing keys in out.
-func flattenJSONPaths(prefix string, m map[string]any, out map[string]any) {
-	for k, v := range m {
-		key := k
-		if prefix != "" {
-			key = prefix + "." + k
-		}
-		switch child := v.(type) {
-		case map[string]any:
-			flattenJSONPaths(key, child, out)
-		case telemetrystoretypes.JSONValue:
-			flattenJSONPaths(key, child, out)
-		default:
-			out[key] = v
-		}
-	}
-}
+
 
 // mergeSpanAttributeColumns merges (attributes_string, attributes_number, attributes_bool, resources_string) into
 // unified "attributes" and "resource" keys, and parses the stringified `events`
@@ -598,7 +582,7 @@ func mergeSpanAttributeColumns(data map[string]any) {
 	resStr, hasRes := data["resources_string"]
 	if hasStr || hasNum || hasBool || attrJSON != nil || hasRes {
 		attributes := make(map[string]any)
-		flattenJSONPaths("", attrJSON, attributes)
+		telemetrystoretypes.FlattenJSONPaths("", attrJSON, attributes)
 		if m, ok := attrStr.(map[string]string); ok {
 			for k, v := range m {
 				attributes[k] = v

pkg/types/spantypes/flamegraph_span.go

@@ -70,6 +70,9 @@ func NewFlamegraphSpanFromStorable(s *StorableSpan, level int64, selectFields []
 	if len(selectFields) == 0 {
 		return span
 	}
+	// The attributes bag (legacy maps + flattened JSON document) is built at most once per
+	// span, on the first attribute-context field — not per field.
+	var attributes map[string]any
 	for _, field := range selectFields {
 		switch field.FieldContext {
 		case telemetrytypes.FieldContextResource:
@@ -77,7 +80,10 @@ func NewFlamegraphSpanFromStorable(s *StorableSpan, level int64, selectFields []
 				span.Resource[field.Name] = v
 			}
 		case telemetrytypes.FieldContextAttribute:
-			if v := s.AttributeValue(field.Name); v != nil {
+			if attributes == nil {
+				attributes = s.Attributes()
+			}
+			if v, ok := attributes[field.Name]; ok && v != nil {
 				span.Attributes[field.Name] = v
 			}
 		}

pkg/types/spantypes/flamegraph_span_test.go

@@ -0,0 +1,85 @@
+package spantypes
+
+import (
+	"testing"
+
+	"github.com/SigNoz/signoz/pkg/types/telemetrystoretypes"
+	"github.com/SigNoz/signoz/pkg/types/telemetrytypes"
+	"github.com/stretchr/testify/assert"
+)
+
+func attributeField(name string) telemetrytypes.TelemetryFieldKey {
+	return telemetrytypes.TelemetryFieldKey{Name: name, FieldContext: telemetrytypes.FieldContextAttribute}
+}
+
+func resourceField(name string) telemetrytypes.TelemetryFieldKey {
+	return telemetrytypes.TelemetryFieldKey{Name: name, FieldContext: telemetrytypes.FieldContextResource}
+}
+
+// The flamegraph selectFields read resolves attribute fields from the span's merged bag, which
+// is built at most once per span no matter how many attribute fields are selected.
+func TestNewFlamegraphSpanFromStorableSelectFields(t *testing.T) {
+	t.Run("attribute fields resolve through the nested json document", func(t *testing.T) {
+		span := &StorableSpan{
+			SpanID: "s1",
+			AttributesJSON: telemetrystoretypes.JSONValue{
+				"http": map[string]any{"route": "/a", "retry": map[string]any{"count": float64(2)}},
+			},
+		}
+
+		flat := NewFlamegraphSpanFromStorable(span, 0, []telemetrytypes.TelemetryFieldKey{
+			attributeField("http.route"), attributeField("http.retry.count"),
+			attributeField("http"), attributeField("missing"),
+		})
+
+		assert.Equal(t, map[string]any{"http.route": "/a", "http.retry.count": float64(2)}, flat.Attributes,
+			"leaves resolve; a parent path and a missing key contribute nothing")
+	})
+
+	t.Run("attribute fields resolve from the legacy maps", func(t *testing.T) {
+		span := &StorableSpan{
+			SpanID:           "s1",
+			AttributesString: map[string]string{"http.route": "/a"},
+			AttributesNumber: map[string]float64{"http.retry.count": 2},
+		}
+
+		flat := NewFlamegraphSpanFromStorable(span, 0, []telemetrytypes.TelemetryFieldKey{
+			attributeField("http.route"), attributeField("http.retry.count"), attributeField("missing"),
+		})
+
+		assert.Equal(t, map[string]any{"http.route": "/a", "http.retry.count": float64(2)}, flat.Attributes)
+	})
+
+	t.Run("json null values are skipped", func(t *testing.T) {
+		span := &StorableSpan{
+			SpanID:         "s1",
+			AttributesJSON: telemetrystoretypes.JSONValue{"k": nil},
+		}
+
+		flat := NewFlamegraphSpanFromStorable(span, 0, []telemetrytypes.TelemetryFieldKey{attributeField("k")})
+
+		assert.Empty(t, flat.Attributes)
+	})
+
+	t.Run("resource fields come from resources_string, empties skipped", func(t *testing.T) {
+		span := &StorableSpan{
+			SpanID:          "s1",
+			ResourcesString: map[string]string{"service.name": "api", "deployment.environment": ""},
+		}
+
+		flat := NewFlamegraphSpanFromStorable(span, 0, []telemetrytypes.TelemetryFieldKey{
+			resourceField("service.name"), resourceField("deployment.environment"), resourceField("missing"),
+		})
+
+		assert.Equal(t, map[string]string{"service.name": "api"}, flat.Resource)
+	})
+
+	t.Run("no selectFields means empty bags", func(t *testing.T) {
+		span := &StorableSpan{SpanID: "s1", AttributesString: map[string]string{"http.route": "/a"}}
+
+		flat := NewFlamegraphSpanFromStorable(span, 0, nil)
+
+		assert.Empty(t, flat.Attributes)
+		assert.Empty(t, flat.Resource)
+	})
+}

pkg/types/spantypes/waterfall_span.go

@@ -8,6 +8,7 @@ import (
 	"time"
 
 	"github.com/SigNoz/signoz/pkg/errors"
+	"github.com/SigNoz/signoz/pkg/types/telemetrystoretypes"
 	"github.com/SigNoz/signoz/pkg/types/telemetrytypes"
 )
 
@@ -103,7 +104,11 @@ type StorableSpan struct {
 	AttributesString   map[string]string  `ch:"attributes_string"`
 	AttributesNumber   map[string]float64 `ch:"attributes_number"`
 	AttributesBool     map[string]bool    `ch:"attributes_bool"`
-	ResourcesString    map[string]string  `ch:"resources_string"`
+	// AttributesJSON is the `attributes` JSON column: post-rollout rows carry their attributes
+	// here instead of the maps above. The trace span reads suppress whichever home is empty per
+	// row, so at most one of (maps, AttributesJSON) holds data for a span.
+	AttributesJSON    telemetrystoretypes.JSONValue `ch:"attributes"`
+	ResourcesString   map[string]string             `ch:"resources_string"`
 	Events             []string           `ch:"events"`
 	StatusMessage      string             `ch:"status_message"`
 	StatusCodeString   string             `ch:"status_code_string"`
@@ -264,21 +269,13 @@ func (ws *WaterfallSpan) getPathToSelectedSpanID(selectedSpanID string) ([]strin
 	return nil, false
 }
 
-func (item *StorableSpan) AttributeValue(name string) any {
-	if v, ok := item.AttributesString[name]; ok {
-		return v
-	}
-	if v, ok := item.AttributesNumber[name]; ok {
-		return v
-	}
-	if v, ok := item.AttributesBool[name]; ok {
-		return v
-	}
-	return nil
-}
-
 func (item *StorableSpan) Attributes() map[string]any {
-	attributes := make(map[string]any, len(item.AttributesString)+len(item.AttributesNumber)+len(item.AttributesBool))
+	attributes := make(map[string]any, len(item.AttributesString)+len(item.AttributesNumber)+len(item.AttributesBool)+len(item.AttributesJSON))
+	// The JSON document is flattened in first and the legacy maps laid over it, so a map entry
+	// wins a same-named JSON path — same precedence as the querier's list-view bag.
+	if len(item.AttributesJSON) > 0 {
+		telemetrystoretypes.FlattenJSONPaths("", item.AttributesJSON, attributes)
+	}
 	for k, v := range item.AttributesString {
 		attributes[k] = v
 	}

pkg/types/spantypes/waterfall_span_test.go

@@ -0,0 +1,60 @@
+package spantypes
+
+import (
+	"testing"
+
+	"github.com/SigNoz/signoz/pkg/types/telemetrystoretypes"
+	"github.com/stretchr/testify/assert"
+)
+
+// The trace span reads suppress whichever attribute home is empty per row, so a StorableSpan
+// carries its attributes in the legacy maps or in AttributesJSON, never duplicated. The merged
+// bag must stay the flat dotted-key shape regardless.
+func TestStorableSpanAttributesHomes(t *testing.T) {
+	t.Run("map-only span reads from the legacy maps", func(t *testing.T) {
+		span := &StorableSpan{
+			AttributesString: map[string]string{"http.route": "/a"},
+			AttributesNumber: map[string]float64{"http.retry.count": 2},
+			AttributesBool:   map[string]bool{"http.cache.hit": true},
+		}
+
+		assert.Equal(t, map[string]any{
+			"http.route":       "/a",
+			"http.retry.count": float64(2),
+			"http.cache.hit":   true,
+		}, span.Attributes())
+	})
+
+	t.Run("json-only span flattens the nested document", func(t *testing.T) {
+		span := &StorableSpan{
+			AttributesJSON: telemetrystoretypes.JSONValue{
+				"http":      map[string]any{"route": "/a", "retry": map[string]any{"count": float64(2)}},
+				"cache.hit": true,
+			},
+		}
+
+		assert.Equal(t, map[string]any{
+			"http.route":       "/a",
+			"http.retry.count": float64(2),
+			"cache.hit":        true,
+		}, span.Attributes())
+	})
+
+	t.Run("map entries win over same-named json paths", func(t *testing.T) {
+		span := &StorableSpan{
+			AttributesString: map[string]string{"http.route": "/a"},
+			AttributesJSON:   telemetrystoretypes.JSONValue{"http": map[string]any{"route": "/stale"}},
+		}
+
+		assert.Equal(t, "/a", span.Attributes()["http.route"])
+	})
+
+	t.Run("empty json document contributes nothing", func(t *testing.T) {
+		span := &StorableSpan{
+			AttributesString: map[string]string{"http.route": "/a"},
+			AttributesJSON:   telemetrystoretypes.JSONValue{},
+		}
+
+		assert.Equal(t, map[string]any{"http.route": "/a"}, span.Attributes())
+	})
+}

pkg/types/telemetrystoretypes/json_flatten.go

@@ -0,0 +1,22 @@
+package telemetrystoretypes
+
+// FlattenJSONPaths flattens a decoded JSON document into dotted keys (a nested {"http":{"route":x}}
+// becomes "http.route": x), matching the legacy map representation so the attributes bag has the
+// same flat shape whether it was read from the maps or the JSON column. Existing keys are
+// overwritten, so a JSON path wins over a same-named map entry.
+func FlattenJSONPaths(prefix string, m map[string]any, out map[string]any) {
+	for k, v := range m {
+		key := k
+		if prefix != "" {
+			key = prefix + "." + k
+		}
+		switch child := v.(type) {
+		case map[string]any:
+			FlattenJSONPaths(key, child, out)
+		case JSONValue:
+			FlattenJSONPaths(key, child, out)
+		default:
+			out[key] = v
+		}
+	}
+}