Failed to store JSON into MySQL-CodePudding

I would like to store this JSON into MySQL

{
    "tokenId": 1,
    "uri": "ipfs://baf...",
    "minPrice": {
        "type": "BigNumber",
        "hex": "0x1a"
    },
    "signature": "0x288..."
}

My insert code

  app.post("/api/create", (req, res) => {
        const minPrice = req.body.minPrice;
        const uri = req.body.uri;
        const tokenId = req.body.tokenId;
        const signature = req.body.signature;
    
        db.query("INSERT INTO Details (tokenId,uri,minPrice,signature) VALUES (?,?,?,?)", [tokenId, uri, minPrice, signature], (err, result) => {
            if (err) {
                console.log(err)
            }
            console.log(result)
        });
    })

Error

 Error: Column count doesn't match value count at row 1
    at Packet.asError (C:\Users\nick_\VSCodeProjects\xxx\nft\node_modules\mysql2\lib\packets\packet.js:728:17)
    at Query.execute (C:\Users\nick_\VSCodeProjects\xxx\nft\node_modules\mysql2\lib\commands\command.js:29:26)
    at Connection.handlePacket (C:\Users\nick_\VSCodeProjects\xxx\nft\node_modules\mysql2\lib\connection.js:487:32)
    at PacketParser.onPacket (C:\Users\nick_\VSCodeProjects\xxx\nft\node_modules\mysql2\lib\connection.js:94:12)
    at PacketParser.executeStart (C:\Users\nick_\VSCodeProjects\xxx\nft\node_modules\mysql2\lib\packet_parser.js:75:16)
    at Socket.<anonymous> (C:\Users\nick_\VSCodeProjects\xxx\nft\node_modules\mysql2\lib\connection.js:101:25)
    at Socket.emit (node:events:513:28)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10) {
  code: 'ER_WRONG_VALUE_COUNT_ON_ROW',
  errno: 1136,
  sqlState: '21S01',
  sqlMessage: "Column count doesn't match value count at row 1",
  sql: "INSERT INTO Details (tokenId,uri,minPrice,signature,) VALUES (10,'ipfs://baf...',`type` = 'BigNumber', `hex` = '0x1a','0x0f...')"
}

My table structure is

id
tokenId    int
uri        varchar(255)
minPrice   json
signature  text

I try to console minPrice, this is what it look like

{ type: 'BigNumber', hex: '0x1a' }

CodePudding user response：

You need to use JSON.stringify() to convert the object to JSON.

const minPrice = JSON.stringify(req.body.minPrice);