Fetch object of datatypes.JSONType return Scan error on column: Failed to unmarshal JSONB value

[shelly-li-sl]

Fetch a JSON object created using datatypes.JSONType[map[string]any], it reported the following error:

=== Fetch code:

        var byExists JsonGenericObjectOnly
	if err := DB.Where(`JSON_EXISTS("obj", '$?(@.env == "qa")')`).First(&byExists).Error; err != nil {
		t.Fatalf("JSON_EXISTS on obj failed: %v", err)
	}

=== Error:
2025/10/22 20:36:15 /scratch/shulili/GORM/gorm-github/gorm-oracle/tests/json_bulk_test.go:482 sql: Scan error on column index 2, name "obj": Failed to unmarshal JSONB value:{"count":7,"env":"qa"}
[11.803ms] [rows:1] SELECT * FROM "json_generic_object_onlies" WHERE JSON_EXISTS("obj", '$?(@.env == "qa")') ORDER BY "json_generic_object_onlies"."record_id" FETCH NEXT 1 ROW ONLY
json_bulk_test.go:483: JSON_EXISTS on obj failed: sql: Scan error on column index 2, name "obj": Failed to unmarshal JSONB value:{"count":7,"env":"qa"}
--- FAIL: TestJSONObject (0.18s)

The above SQL generated to fetch the record is correct, it could return the correct record if executed in SQLPlus.

=== Full test case(the test case is added into json_bulk_test.go)

func TestJSONObject(t *testing.T) {
	type JsonGenericObjectOnly struct {
		ID     uint                                `gorm:"primaryKey;autoIncrement;column:record_id"`
		Name   string                              `gorm:"column:name"`
		Obj    datatypes.JSONType[map[string]any]  `gorm:"column:obj"`
		ObjPtr *datatypes.JSONType[map[string]any] `gorm:"column:obj_ptr"`
	}

	DB.Migrator().DropTable(&JsonGenericObjectOnly{})
	if err := DB.Set("gorm:table_options", "TABLESPACE SYSAUX").AutoMigrate(&JsonGenericObjectOnly{}); err != nil {
		t.Fatalf("migrate failed: %v", err)
	}

	// INSERT
	obj := datatypes.NewJSONType(map[string]any{"env": "qa", "count": 7})
	obj2 := datatypes.NewJSONType(map[string]any{"owner": "team-z"})
	rec := JsonGenericObjectOnly{
		Name:   "json-generic-obj-only",
		Obj:    obj,
		ObjPtr: &obj2,
	}
	if err := DB.Create(&rec).Error; err != nil {
		t.Fatalf("create failed: %v", err)
	}
	if rec.ID == 0 {
		t.Fatalf("expected ID set")
	}

	// Check the inserted object
	var byExists JsonGenericObjectOnly
	if err := DB.Where(`JSON_EXISTS("obj", '$?(@.env == "qa")')`).First(&byExists).Error; err != nil {
		t.Fatalf("JSON_EXISTS on obj failed: %v", err)
	}
	if byExists.ID != rec.ID {
		t.Fatalf("unexpected row found by JSON_EXISTS(obj): %#v", byExists)
	}

	// UPDATE object using JSON_TRANSFORM: set/overwrite a property
	if err := DB.
		Model(&JsonGenericObjectOnly{}).
		Where(`"record_id" = ?`, rec.ID).
		Update("obj", gorm.Expr(`JSON_TRANSFORM("obj", SET '$.count' = ?)`, 8)).Error; err != nil {
		t.Fatalf("JSON_TRANSFORM(obj SET ...) failed: %v", err)
	}

	// VERIFY
	var countVal int
	if err := DB.Model(&JsonGenericObjectOnly{}).
		Select(`JSON_VALUE("obj", '$.count')`).
		Where(`"record_id" = ?`, rec.ID).
		Scan(&countVal).Error; err != nil {
		t.Fatalf("scan JSON_VALUE(obj.count) failed: %v", err)
	}
	if countVal != 8 {
		t.Fatalf("unexpected obj.count: %d", countVal)
	}

	// DELETE
	var deleted []JsonGenericObjectOnly
	if err := DB.
		Where(`"record_id" = ?`, rec.ID).
		Clauses(clause.Returning{Columns: []clause.Column{
			{Name: "record_id"}, {Name: "name"}, {Name: "obj"}, {Name: "obj_ptr"},
		}}).
		Delete(&deleted).Error; err != nil {
		t.Fatalf("delete returning failed: %v", err)
	}
	if len(deleted) != 1 || deleted[0].ID != rec.ID {
		t.Fatalf("unexpected deleted rows: %#v", deleted)
	}

	// VERIFY gone
	var check JsonGenericObjectOnly
	err := DB.First(&check, `"record_id" = ?`, rec.ID).Error
	if !errors.Is(err, gorm.ErrRecordNotFound) {
		t.Fatalf("expected not found after delete, got: %v", err)
	}
}