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
Closed this issue · 6 comments
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
Describe the bug
A clear and concise description of what the bug is.
To Reproduce
Steps to reproduce the behavior:
- Add package with configuration '...'
- Send request with options '...'
- See error
Expected behavior
A clear and concise description of what you expected to happen.
Environment
- Vapor Framework version:
- Vapor Toolbox version:
- OS version:
Additional context
Add any other context about the problem here.
@rnrajput Sorry, but all the local telepaths are on vacation right now, dude.
¯\(ツ)/¯
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
}
}@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
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\"}}" }
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.