siyuan-note/siyuan · error

SQL value must be a JSON scalar

Error message

SQL value must be a JSON scalar

What it means

SQLValue wraps a single cell of a SQL query result and only accepts JSON scalars (numbers, strings, booleans, null). Its UnmarshalJSON trims whitespace and rejects input that is not valid JSON or that starts with '{' or '[', returning "SQL value must be a JSON scalar". Rows are represented as map[string]SQLValue, so any object or array in the data stream violates the row contract and must be decoded differently.

Solutions

1. Flatten nested values into scalar columns (stringified JSON, separate columns, or individual fields) before unmarshaling into SQLRows
2. Change the cell producer so each column emits a scalar (number, string, bool, or null)
3. If the value must stay structured, decode the row into map[string]json.RawMessage or a dedicated struct instead of SQLRows
4. Inspect the offending row/cell (first '{' or '[') and adjust the query with json_extract to pull out scalars

Example fix

// before
rows := SQLRows{{"meta": mustJSON(map[string]any{"a": 1})}} // cell starts with '{'
// after
rows := SQLRows{{"meta": rawMessage("\"{\\\"a\\\":1}\"")}} // scalar string, or split into scalar columns

func isScalarCell(v any) bool {
  switch t := v.(type) {
  case nil, string, bool, float64, int64, int: return true
  default: return false
  }
}

func isJSONScalar(raw []byte) bool {
  d := bytes.TrimSpace(raw)
  return len(d) > 0 && json.Valid(d) && d[0] != '{' && d[0] != '['
}

var v SQLValue
if err := json.Unmarshal(cell, &v); err != nil { if strings.Contains(err.Error(), "SQL value must be a JSON scalar") { return flattenCell(cell) }; return err }

When it happens

Trigger: json.Unmarshal / json.Decode into SQLValue or SQLRows ([]map[string]SQLValue) when a cell contains a JSON object or array — e.g. row data produced by marshaling map[string]any where a column holds a nested structure, or a query result embedding JSON documents in a column.

Common situations: A SQLite column stores a JSON blob and the query returns it as an object; the producer marshals struct fields that contain slices/maps into each cell instead of scalars; an upstream API nests objects per cell; a test fixture builds rows with array values.

Understand the failure class

Background: Type mismatch errors: IllegalArgumentException, TypeError and type guards across 150 open-source libraries — this error's family across 150 libraries.

Related errors

• block ID must be text
• block operation result must be text, block IDs or null
• createDocTree definition must be a list
• createDocTree document must be a dictionary
• Field [id] should be of type [String]

AI-assisted analysis of siyuan-note/siyuan@9f775e8a12 (2026-09-19). Data as JSON: /api/errors/708435d34b5f0209. Report an issue: GitHub.

Appendix: source

Thrown at kernel/apicontract/query.go:27

type SQLQueryRequest struct {
	Stmt string `json:"stmt" api:"trim"`
	Mode string `json:"mode" api:"optional,nullable"`
}

// SQLValue 保留数据库标量的 JSON 表示，包括整数精度和二进制值的 Base64 文本。
type SQLValue struct{ raw json.RawMessage }

func (v SQLValue) MarshalJSON() ([]byte, error) {
	if len(v.raw) == 0 {
		return []byte("null"), nil
	}
	return v.raw, nil
}

func (v *SQLValue) UnmarshalJSON(data []byte) error {
	data = bytes.TrimSpace(data)
	if !json.Valid(data) || data[0] == '{' || data[0] == '[' {
		return fmt.Errorf("SQL value must be a JSON scalar")
	}
	v.raw = append(v.raw[:0], data...)
	return nil
}

type SQLRows []map[string]SQLValue

type SQLQueryLimit struct {
	Limit     int  `json:"limit"`
	Truncated bool `json:"truncated"`
}

// SuccessSQL 将限额信息保留在信封顶层，查询结果仍位于 data。
func SuccessSQL(rows SQLRows, limit int, truncated bool) Response[SQLRows] {
	return Response[SQLRows]{data: rows, queryLimit: &SQLQueryLimit{Limit: limit, Truncated: truncated}}
}

View on GitHub (pinned to 9f775e8a12)