为什么在将 JSON 对象插入 mssql 表(Node.js)后获得 [对象对象]

Question

我正在构建一个API，在我发出发布请求后，我正在尝试将Json obj插入我的Azure sql db。我得到这个结果：

例

我的插入用户.sql ：

CREATE procedure [dbo].[insertUser]  
(  
@username varchar(50),  
@email nvarchar(max),  
@password nvarchar(max),
@hobbies nvarchar(max)   
)  
AS  
BEGIN  
insert into users (username,email,password,hobbies) values( @username, @email, @password,@hobbies)  
END  

我的创建用户表.sql

IF OBJECT_ID('Users.users', 'U') IS NOT NULL
DROP TABLE Users.users
GO
-- Create the table in the specified schema
CREATE TABLE users
(
username [VARCHAR](50) NOT NULL PRIMARY KEY, -- primary key column
email [NVARCHAR](MAX) NOT NULL,
password [NVARCHAR](MAX) NOT NULL,
hobbies [NVARCHAR](MAX) NOT NULL 
-- specify more columns here
);
GO

我的路线用户.js：

const express = require('express');
const router=express.Router();
const { poolPromise } = require('../../config/db')  
const sql = require('mssql')  
const bcrypt = require ('bcryptjs');
const {check, validationResult} = require('express-validator')

//@route POST/api/users 
//@desc Insert new user
//@access Public
router.post('/',[
check('username', 'Name is required').not().isEmpty(),
check('email', 'Please include a valid e-mail').isEmail(),
check('password', 'Please enter a password with 8 or more characters').isLength({min:8}).isAlpha(),
check('hobbies', 'hobbies required').not().isEmpty(),
],
async (req,res)=>  {  
try{
const errors = validationResult(req)
if(!errors.isEmpty()){
return res.status(400).json({ errors: errors.array() });
}
const {username,password,email,hobbies} = req.body;
//Password bcrypt
const salt= await bcrypt.genSalt(10);
cryptpassword = await bcrypt.hash(password,salt);
//insert to DB
const pool = await poolPromise  
const result = await pool.request()
.input("username",sql.VarChar(50), username)
.input("email", sql.VarChar(50),email)
.input("password",sql.VarChar(50),cryptpassword)
.input("hobbies", sql.NVarChar('max'),hobbies)
.execute("InsertUser").then(function (recordSet){
res.status(200).json({ status: "Success" })  
})  
}
catch(error){
console.error(error.message)
res.status(500).send('Server error');
}

});
module.exports = router;

在邮递员中，我正在尝试：

{
"username":"davidzz12z1",
"email":"[e-mail]",
"password":"asdasdadadsadsad",
"hobbies":[
{"first": "football"},
{"second": "baskteball"}
]
}

似乎我应该定义我的程序来获取一些 JSON 文件，但我没有找到如何做到这一点。

Answer 1

您需要转义爱好属性的内容：

从

[
{"first": "football"},
{"second": "baskteball"}
]

自：

"[n        {"first": "football"},n        {"second": "baskteball"}n            ]"

作为另一种选择，只需用单引号包装内容：

'[
{"first": "football"},
{"second": "baskteball"}
]'

https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15#import-json-data-into-sql-server-tables