googleapis/mcp-toolbox · error

unable to unmarshal json data for column %s

Error message

unable to unmarshal json data for column %s

What it means

This error occurs when a column typed as Oracle JSON was scanned into sql.RawBytes but its bytes are not valid JSON, so json.Unmarshal fails. RunSQL routes columns whose DatabaseTypeName() is "JSON" into a RawBytes receiver and parses them to return structured JSON rather than a raw string. Note the column name is interpolated but the underlying unmarshal error is not included in the message.

Source

Thrown at internal/sources/oracle/oracle.go:242

					vMap[col] = nil
				}
			case *sql.NullString:
				if v.Valid {
					vMap[col] = v.String
				} else {
					vMap[col] = nil
				}
			case *sql.NullTime:
				if v.Valid {
					vMap[col] = v.Time
				} else {
					vMap[col] = nil
				}
			case *sql.RawBytes:
				if *v != nil {
					var unmarshaledData any
					if err := json.Unmarshal(*v, &unmarshaledData); err != nil {
						return nil, fmt.Errorf("unable to unmarshal json data for column %s", col)
					}
					vMap[col] = unmarshaledData
				} else {
					vMap[col] = nil
				}
			default:
				return nil, fmt.Errorf("unexpected receiver type: %T", v)
			}
		}
		out = append(out, vMap)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("errors encountered during query execution or row processing: %w", err)
	}

	return out, nil
}

View on GitHub (pinned to 8cc6e09de2)

Solutions

1. Inspect the offending column's value for that row — validate it with SELECT JSON_SERIALIZE(col) or check IS JSON to find corrupt rows.
2. Cast the column to a string in the query (e.g. TO_CHAR(col) or col) via a non-JSON alias so it is returned as text instead of being unmarshaled.
3. Add or fix the CHECK (col IS JSON) constraint to prevent invalid JSON from being stored.
4. Check for byte truncation (network MTU, LOB fetch limits, driver LOB settings) that could cut JSON payloads mid-document.
5. Upgrade go-ora/godror if a known bug reports wrong type names for VARCHAR2 columns holding JSON.

Example fix

// before: returns invalid column value as text
col varchar2(4000) = '{broken'

// after: enforce JSON at the schema level
ALTER TABLE t ADD CONSTRAINT t_json_chk CHECK (jcol IS JSON);

SELECT COUNT(*) FROM t WHERE NOT jcol IS JSON;
-- returns rows containing invalid JSON; fix or exclude them before querying through RunSQL

out, err := source.RunSQL(ctx, stmt, params, true)
if err != nil {
    if strings.Contains(err.Error(), "unable to unmarshal json data for column") {
        col := extractColName(err.Error())
        // fall back: re-query casting the JSON column to a plain string
        return source.RunSQL(ctx, "SELECT TO_CHAR("+col+") AS "+col+", ... FROM ...", params, true)
    }
    return err
}

When it happens

Trigger: RunSQL (readOnly=true) selecting from a column reported as type JSON whose content is malformed or truncated (e.g. a value stored via legacy paths, truncated by max byte limits, or a driver that reports JSON for a VARCHAR2 column containing non-JSON text).

Common situations: Oracle 21c+ JSON-type columns populated with invalid data before a CHECK 'IS JSON' constraint existed; middleware truncating large JSON documents; a go-ora driver mislabeling a VARCHAR2/CLOB as JSON; hand-inserted strings into an IS-JSON disabled column.

Related errors

• could not unmarshal response as json: %w
• failed to unmarshal operation JSON to map: %w
• failed to unmarshal response: %v
• failed to unmarshal json: %v
• unable to unmarshal JSON: %w

AI-assisted analysis of googleapis/mcp-toolbox@8cc6e09de2 (2026-09-05). Data as JSON: /api/errors/8148850083d97d1b. Report an issue: GitHub.