Swift Vapor-Save records in Mysql JSON data type during mapping with swift get error- Could not convert MySQL data to String: <MYSQL_TYPE_JSON about vapor

[rnrajput]

@mkll thanks now is working in mysql changes as LONGTEXT

[mkll]

@rnrajput Sorry, but all the local telepaths are on vacation right now, dude.
¯\(ツ)/¯

[rnrajput]

func prepare(on database: Database) async throws { try await database.schema("master_Interface_tb") .id() .field("customer_id", .string, .required) .field("page_name", .string,.required) .field("page_json", .custom("JSONB"), .required) .create() } final class MasterInterface: Model, Content { static let schema = "master_Interface_tb" @id(key: .id) var id: UUID? @field(key:"customer_id") var customerId: String @field(key:"page_name") var pageName: String @field(key:"page_json") var pageJson: String? init() { } init(id: UUID? = nil, customerId: String, pageName: String, pageJson:String?) { self.id = id self.customerId = customerId self.pageName = pageName self.pageJson = pageJson } }

[mkll]

@rnrajput First, we format your code:

func prepare(on database: Database) async throws {
	try await database.schema("master_Interface_tb")
		.id()
		.field("customer_id", .string, .required)
		.field("page_name", .string,.required)
		.field("page_json", .custom("JSONB"), .required)
		.create()
}

final class MasterInterface: Model, Content {
	static let schema = "master_Interface_tb"
	
	@id(key: .id) var id: UUID?
	@field(key:"customer_id") var customerId: String
	@field(key:"page_name") var pageName: String
	@field(key:"page_json") var pageJson: String?
	
	init() { }
	
	init(id: UUID? = nil, customerId: String, pageName: String, pageJson:String?) {
		self.id = id
		self.customerId = customerId
		self.pageName = pageName
		self.pageJson = pageJson
	}
}

Next, let's look at the correspondence between the table schema and the corresponding model.

We can see that for page_json column name you have JSON definition in your schema and String definition in your model, and that the error message you receive matches your code exactly. It couldn't be any other way.

JSON definition in a schema is used when another nested model is stored in the corresponding model variable. In this case, the database driver automatically encodes and decodes the entire model hierarchy.

If you have a String property in your model, then in the schema the corresponding field should also be defined as String.

You should read the relevant basic sections of the documentation:
https://docs.vapor.codes/fluent/schema/#dictionary

[rnrajput]

page_json column name as a JSON string . it is not JSON, same things work on when i post data it works successful { "customerId":"AB02", "pageName": "LoginPage", "pageJson": "{\"loginPage\":{\"navigationTitle\":\"LoginPage\",\"navigationFontSize\":20,\"navigationTitleColor\":\"#000000\",\"alignment\":\"center\"}}" }

[mkll]

page_json column name as a JSON string . it is not JSON

The MySQL 8.0 documentation says the following:

"JSON documents stored in JSON columns are converted to an internal format that permits quick read access to document elements. When the server later must read a JSON value stored in this binary format, the value need not be parsed from a text representation. The binary format is structured to enable the server to look up subobjects or nested values directly by key or array index without reading all values before or after them in the document."

So, it's not a "JSON string", it's internal JSON representation and cannot be read as String. Exactly the same thing the error message says ("Could not convert MySQL data to String: <MYSQL_TYPE_JSON").

If you want to write JSON as a string, want to read it as a string and work with it as a string, then you should define a .string data type in the column rather than JSON. This is exactly how it works.