如何将 JSON 插入 Azure SQL DB in Node.js (REST API)

Question

我正在尝试使用很少的get\post方法构建新的REST API，我的问题是：如何构建我的表以支持json？ 以及我应该写什么来插入我从用户到我的数据库的特殊 JSON

数据库.js：

require("dotenv").config();
const sql = require("mssql")
// Create connection to database
const config = {
userName: process.env.tedious_userName,
password: process.env.tedious_password,
server:process.env.tedious_server,
database:process.env.tedious_database
};
const connection = new Connection(config);
// Attempt to connect and execute queries if connection goes through
module.exports =connection.on("connect", err => {
if (err) {
console.error(err.message);
});
module.exports = connectDB 

现在我想创建一些从用户那里获取新配方的 post 方法，所以我得到了这个 JSON：

[
{
"username": "newuser",
"id": 1,
"name": "Hamburger",
"img": "https://image.shutterstock.com/w-705104968.jpg",
"time": 45,
"likes": 17,
"isGluten": false,
"isVegaterian": false,
"isWatched": false,
"isSave": false,
"ingredients": [
{
"amount": 5,
"product": "pound beef short ribs"
},
{
"amount": 2,
"product": "teaspoon salt"
},
{
"amount": 1.5,
"product": "tablespoons all-purpose flour"
},
{
"amount": 0.5,
"product": "teaspoon ground black pepper"
}
],
"instructions": [
{
"Step": "Preheat oven to 350 degrees F (175 degrees C). Grease and flour a 9x9 inch pan or line a            muffin pan with paper liners."
},
{
"Step": "In a medium bowl, cream together the sugar and butter. Beat in the eggs, one at a time, then stir in the vanilla. Combine flour and baking powder, add to the creamed mixture and mix well. Finally stir in the milk until batter is smooth. Pour or spoon batter into the prepared pan."
},
{
"Step": "Bake for 30 to 40 minutes in the preheated oven. For cupcakes, bake 20 to 25 minutes. Cake is done when it springs back to the touch."
}
]
}
]

我需要一些帮助来定义支持 json 文件的表，并将上面的这个 json 插入到这个表中。

Answer 1

1.您可以创建如下表格：

create table myTable
(
Id int identity primary key,
Data nvarchar(max) 
)

"数据"列是存储 JSON 数据的位置。

2.创建如下所示的存储过程：

create procedure InsertJSON(@json nvarchar(max))
as begin
insert into myTable(Data)
values(@json)
end

3.执行存储过程

例如。

exec InsertJSON '{"Price":10455,"Color":"White","tags": ["toy","children","games"]}'

并检查 JSON 数据是否已存储到 myTable 中

4.尝试使用内置的 JSON_VALUE((、JSON_QUERY(( 查询 JSON 数据

select JSON_VALUE(Data, '$.Price') from myTable

最后你可以看看这个链接