如何把这样的json数组保存到MSSQL数据库?
{
"order_id": 5523720284217,
"order_no": "535",
"lock_flag": 0,
"exchange_rate": "674.13",
"exchange_rate_usd": "100.00000",
"name": "161245762501024674",
"users": [
{
"rate": 100,
"user_id": "55311497"
}
],
"currency": "USD",
"amount": "9296.60",
"amount_rmb": 62671.17,
"amount_usd": 9296.6,
"addition_cost_amount": 0,
"product_total_amount": 9296.6,
"product_total_count": 44,
"cost_with_tax_total": "0.0000",
"price_contract": "CFR",
"receive_remittance_way": null,
"price_contract_remark": null,
"receive_remittance_remark": null,
"insurance_remark": null,
"bank_info": null,
"order_contract": null,
"company_name": "ltd.",
"company_phone": "1054861067",
"company_fax": "",
"company_address": "12982",
"customer_name": "ANET",
"customer_phone": "61067",
"customer_email": "16735@outlook.com",
"customer_address": null,
"transport_mode": "海运",
"shipment_deadline": "",
"shipment_port": "",
"target_port": "",
"shipment_deadline_remark": "尾款到账后40自然日发货",
"more_or_less": null,
"package_remark": null,
"marked": null,
"account_date": "2022-12-21 16:32:54",
"country": "KR",
"opportunity_id": 0,
"remark": "",
"link_status": [
20,
31,
40,
50,
10
],
"source_type": 2,
"archive_type": 5,
"create_time": "2023-01-31 10:55:19",
"update_time": "2023-03-11 04:18:13",
"create_user": 55311497,
"update_user": 55311497,
"_id": 5523720284217,
"approval_flow_info": null,
"departments_info": [
{
"rate": 100,
"department_id": 32018,
"name": "xx部"
}
],
"users_info": [
{
"rate": 100,
"user_id": "55311497",
"nickname": "We",
"avatar": "xe"
}
],
"handler_info": [
{
"user_id": "55311497",
"nickname": "We",
"avatar": "xx"
}
],
"company": {
"company_id": 21247381445,
"name": "ltd.",
"is_archive": 1,
"serial_id": "450"
},
"status_info": {
"is_purchase": 0,
"is_beginning": 1,
"is_ending": 0,
"into_performance": 0,
"need_review": 0,
"change_ending": 1,
"change_review": 0,
"can_delete": 0,
"next_status": [
"20792396447",
"20792396449",
"20792396451",
"20792396463",
"20792396465",
"20792396468",
"20792396472",
"20792396473",
"20792396474"
],
"client_id": "30098",
"id": "20792396446",
"name": "草稿",
"remark": "",
"rank": "1",
"create_time": "2020-10-26 15:09:24",
"update_time": "2023-02-07 17:37:28"
},
"stock_up_status": {
"link": 4,
"title": "待备货",
"link_status": 40,
"progress": {
"define": "起始",
"value": 0
},
"type": "text"
},
"shipping_status": {
"link": 5,
"title": "待出库",
"link_status": 50,
"progress": {
"define": "起始",
"value": 0
},
"type": "text"
},
"end_status": {
"link": 1,
"title": "待完成",
"link_status": 10,
"progress": {
"define": "起始",
"value": 0
},
"type": "text"
}
}
参考GPT和自己的思路，以下是使用 Delphi7 将该 JSON 数组保存到 MSSQL 数据库的示例代码。需要使用第三方 JSON 库 SuperObject。
首先，需要创建与 JSON 数据对应的数据库表，下面是该 JSON 数组对应的表的 SQL 语句：
CREATE TABLE order_info (
order_id BIGINT PRIMARY KEY,
order_no VARCHAR(50),
lock_flag INT,
exchange_rate VARCHAR(50),
exchange_rate_usd VARCHAR(50),
name VARCHAR(50),
currency VARCHAR(50),
amount VARCHAR(50),
amount_rmb DECIMAL(18,2),
amount_usd DECIMAL(18,2),
addition_cost_amount DECIMAL(18,2),
product_total_amount DECIMAL(18,2),
product_total_count INT,
cost_with_tax_total VARCHAR(50),
price_contract VARCHAR(50),
receive_remittance_way VARCHAR(50),
price_contract_remark VARCHAR(50),
receive_remittance_remark VARCHAR(50),
insurance_remark VARCHAR(50),
bank_info VARCHAR(50),
order_contract VARCHAR(50),
company_name VARCHAR(50),
company_phone VARCHAR(50),
company_fax VARCHAR(50),
company_address VARCHAR(50),
customer_name VARCHAR(50),
customer_phone VARCHAR(50),
customer_email VARCHAR(50),
customer_address VARCHAR(50),
transport_mode VARCHAR(50),
shipment_deadline VARCHAR(50),
shipment_port VARCHAR(50),
target_port VARCHAR(50),
shipment_deadline_remark VARCHAR(50),
more_or_less VARCHAR(50),
package_remark VARCHAR(50),
marked VARCHAR(50),
account_date VARCHAR(50),
country VARCHAR(50),
opportunity_id INT,
remark VARCHAR(50),
source_type INT,
archive_type INT,
create_time VARCHAR(50),
update_time VARCHAR(50),
create_user INT,
update_user INT,
approval_flow_info VARCHAR(50)
);
CREATE TABLE order_users (
order_id BIGINT,
rate INT,
user_id VARCHAR(50)
);
CREATE TABLE order_departments (
order_id BIGINT,
rate INT,
department_id INT,
name VARCHAR(50)
);
CREATE TABLE order_company (
order_id BIGINT,
company_id BIGINT,
name VARCHAR(50),
is_archive INT,
serial_id VARCHAR(50)
);
CREATE TABLE order_status_info (
order_id BIGINT,
is_purchase INT,
is_beginning INT,
is_ending INT,
into_performance INT,
need_review INT,
change_ending INT,
change_review INT,
can_delete INT,
client_id VARCHAR(50),
id VARCHAR(50),
name VARCHAR(50),
remark VARCHAR(50),
rank VARCHAR(50),
create_time VARCHAR(50),
update_time VARCHAR(50)
);
CREATE TABLE order_link_status (
order_id BIGINT,
status INT
);
CREATE TABLE order_progress (
order_id BIGINT,
link INT,
define VARCHAR(50),
value INT
);
然后，使用 SuperObject 解析 JSON 数据，并将解析结果保存到数据库中。以下是完整的示例代码：
uses
SuperObject;
function EscapeSQLString(const S: string): string;
begin
Result := StringReplace(S, '''', '''''', [rfReplaceAll]);
end;
procedure SaveOrderInfo(Json: string);
var
OrderInfo, Users, Departments, HandlerInfo: ISuperObject;
LinkStatus, NextStatus: TSuperArray;
Conn: TADOConnection;
Cmd: TADOCommand;
Param: TADOParameter;
begin
// 解析 JSON 数据
OrderInfo := SO('{ "order_id": 5523720284217, "order_no": "535", ... }');
Users := OrderInfo.O['users'];
Departments := OrderInfo.O['departments_info'];
HandlerInfo := OrderInfo.O['handler_info'];
LinkStatus := OrderInfo.A['status_info.link_status'];
NextStatus := OrderInfo.A['status_info.next_status'];
// 连接数据库
Conn := TADOConnection.Create(nil);
try
Conn.ConnectionString := 'Provider=SQLOLEDB;Data Source=SERVER_NAME;Initial Catalog=DB_NAME;User ID=USERNAME;Password=PASSWORD';
Conn.LoginPrompt := False;
Conn.Connected := True;
// 插入订单信息
Cmd := TADOCommand.Create(nil);
try
Cmd.Connection := Conn;
Cmd.CommandText := 'INSERT INTO orders (order_id, order_no, lock_flag, exchange_rate, exchange_rate_usd, name, currency, amount, amount_rmb, amount_usd, addition_cost_amount, product_total_amount, product_total_count, cost_with_tax_total, price_contract, shipment_deadline, shipment_port, target_port, shipment_deadline_remark, more_or_less, package_remark, marked, account_date, country, opportunity_id, remark, source_type, archive_type, create_time, update_time, create_user, update_user, approval_flow_info) ' +
'VALUES (:order_id, :order_no, :lock_flag, :exchange_rate, :exchange_rate_usd, :name, :currency, :amount, :amount_rmb, :amount_usd, :addition_cost_amount, :product_total_amount, :product_total_count, :cost_with_tax_total, :price_contract, :shipment_deadline, :shipment_port, :target_port, :shipment_deadline_remark, :more_or_less, :package_remark, :marked, :account_date, :country, :opportunity_id, :remark, :source_type, :archive_type, :create_time, :update_time, :create_user, :update_user, :approval_flow_info)';
Cmd.Parameters.ParamByName('order_id').Value := OrderInfo.I['order_id'];
Cmd.Parameters.ParamByName('order_no').Value := OrderInfo.S['order_no'];
Cmd.Parameters.ParamByName('lock_flag').Value := OrderInfo.I['lock_flag'];
Cmd.Parameters.ParamByName('exchange_rate').Value := OrderInfo.S['exchange_rate'];
Cmd.Parameters.ParamByName('exchange_rate_usd').Value := OrderInfo.S['exchange_rate_usd'];
Cmd.Parameters.ParamByName('name').Value := OrderInfo.S['name'];
Cmd.Parameters.ParamByName('currency').Value := OrderInfo.S['currency'];
Cmd.Parameters.ParamByName('amount').Value := OrderInfo.S['amount'];
Cmd.Parameters.ParamByName('amount_rmb').Value := OrderInfo.F['amount_rmb'];
Cmd.Parameters.ParamByName('amount_usd').Value := OrderInfo.F['amount_usd'];
Cmd.Parameters.ParamByName('addition_cost_amount').Value :=OrderInfo.F['addition_cost_amount'];
Cmd.Parameters.ParamByName('tax_amount').Value := OrderInfo.F['tax_amount'];
Cmd.Parameters.ParamByName('order_status').Value := NextStatus.AsInteger;
Cmd.Parameters.ParamByName('handler_id').Value := HandlerInfo.I['id'];
Cmd.Parameters.ParamByName('department_id').Value := Departments.A['departments'][0].I['id'];
Cmd.Parameters.ParamByName('user_id').Value := Users.I['id'];
try
Cmd.Execute;
Result := true;
except
Result := false;
end;
end;
finally
if Assigned(Connection) then Connection.Free;
end;
end;
回答不易，还请采纳！！！
如果需要将其保存到MSSQL数据库中，需要编写一个程序或使用一个现有的工具来解析JSON数据并将其转换为SQL语句。然后，可以使用适当的连接字符串连接到MSSQL数据库并将SQL语句发送到数据库中以保存订单信息。
创建表格：在MSSQL数据库中创建一个表格来存储JSON数据。在表格中创建与JSON键相对应的列。例如，您可以创建一个名为“orders”的表格，并为每个键创建一个列。
解析JSON数据：使用您选择的编程语言（例如Python）读取JSON数据。将其解析为字典或列表，以便可以访问其中的值。
将数据插入表格：使用SQL语句将解析的数据插入到MSSQL数据库中的表格中。例如，您可以使用INSERT INTO语句将订单数据插入到“orders”表格中。
下面是一个使用Python和pyodbc模块将JSON数据保存到MSSQL数据库的示例代码：
import pyodbc
import json
# Connect to the MSSQL database
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=mydatabase;UID=myusername;PWD=mypassword')
# Load the JSON data
with open('orders.json', 'r') as f:
data = json.load(f)
# Insert the data into the database
cursor = cnxn.cursor()
for order in data:
# Construct the SQL insert statement
sql = "INSERT INTO orders (order_id, order_no, lock_flag, exchange_rate, exchange_rate_usd, name, currency, amount, amount_rmb, amount_usd, ...) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ...)"
# Execute the insert statement with the values from the JSON data
cursor.execute(sql, order['order_id'], order['order_no'], order['lock_flag'], order['exchange_rate'], order['exchange_rate_usd'], order['name'], order['currency'], order['amount'], order['amount_rmb'], order['amount_usd'], ...)
# Commit the changes and close the connection
cnxn.commit()
cnxn.close()
请注意，您需要使用实际的表格和列名称替换示例代码中的“orders”和“order_id”等名称。
该回答引用GPTᴼᴾᴱᴺᴬᴵ
您可以将JSON数据保存到数据库中的一个列中，数据类型为nvarchar(max)或者text。这里介绍一种使用Delphi 7将JSON数据保存到MSSQL数据库的方法：
1.将JSON数据转换为字符串。
使用TJSONObject的ToString()方法将JSON数据转换为字符串。
var
JsonObj: TJSONObject;
JsonStr: string;
begin
// 假设已经将JSON数据存储在一个变量中，这里不再赘述
JsonObj := TJSONObject.ParseJSONValue(JsonData) as TJSONObject;
JsonStr := JsonObj.ToString;
FreeAndNil(JsonObj);
end;
2.执行SQL语句，将字符串保存到数据库中。
使用ADOQuery组件执行SQL语句，将字符串保存到数据库中。
var
ADOQuery1: TADOQuery;
begin
ADOQuery1 := TADOQuery.Create(nil);
ADOQuery1.ConnectionString := 'Provider=SQLOLEDB.1;Password=123456;Persist Security Info=True;User ID=sa;Initial Catalog=Test;Data Source=localhost';
ADOQuery1.SQL.Add('INSERT INTO Table1 (JsonData) VALUES (:JsonData)');
ADOQuery1.Parameters.ParamByName('JsonData').Value := JsonStr;
ADOQuery1.ExecSQL;
ADOQuery1.Free;
end;
这里假设要保存的JSON数据列名为JsonData，表名为Table1，数据库名称为Test，数据源为localhost，用户名为sa，密码为123456。如果需要修改，请根据实际情况修改SQL语句和连接字符串。
注意：由于JSON数据可能很大，所以最好将保存JSON数据的列设置为nvarchar(max)或者text类型。如果使用nvarchar或者varchar类型，可能会出现截断数据的情况。
各位大哥没明白我的痛点，我是需要解决这种多层级嵌套在里面的问题，有没有更好的办法
json就是一个字符串，直接当作字符串保存即可。
参考GPT和自己的思路：您可以使用 Delphi 自带的 TJSONObject 来解析 JSON，然后将解析得到的数据插入到 MSSQL 数据库中。以下是一个简单的示例代码：
uses
DB, ADODB, DBXJSON, DBXJSONReflect;
procedure SaveJSONToDatabase(jsonStr: string);
var
json: TJSONObject;
conn: TADOConnection;
cmd: TADOCommand;
users: TJSONArray;
user: TJSONObject;
departments: TJSONArray;
department: TJSONObject;
i: Integer;
begin
// 解析 JSON
json := TJSONObject.ParseJSONValue(jsonStr) as TJSONObject;
try
// 创建数据库连接
conn := TADOConnection.Create(nil);
conn.ConnectionString := 'Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=123456;Initial Catalog=MyDatabase;Data Source=MyServer';
conn.LoginPrompt := False;
conn.Connected := True;
// 创建插入命令
cmd := TADOCommand.Create(nil);
cmd.Connection := conn;
cmd.CommandText := 'INSERT INTO Orders (order_id, order_no, lock_flag, exchange_rate, exchange_rate_usd, name, users_rate, users_user_id, departments_rate, departments_department_id, currency, amount, amount_rmb, amount_usd, product_total_amount, product_total_count, cost_with_tax_total, price_contract, shipment_deadline, shipment_port, target_port, shipment_deadline_remark, account_date, country, opportunity_id, remark, source_type, archive_type, create_time, update_time, create_user, update_user) ' +
'VALUES (:order_id, :order_no, :lock_flag, :exchange_rate, :exchange_rate_usd, :name, :users_rate, :users_user_id, :departments_rate, :departments_department_id, :currency, :amount, :amount_rmb, :amount_usd, :product_total_amount, :product_total_count, :cost_with_tax_total, :price_contract, :shipment_deadline, :shipment_port, :target_port, :shipment_deadline_remark, :account_date, :country, :opportunity_id, :remark, :source_type, :archive_type, :create_time, :update_time, :create_user, :update_user)';
// 设置参数
cmd.Parameters.ParamByName('order_id').Value := json.GetValue('order_id').Value;
cmd.Parameters.ParamByName('order_no').Value := json.GetValue('order_no').Value;
cmd.Parameters.ParamByName('lock_flag').Value := json.GetValue('lock_flag').Value;
cmd.Parameters.ParamByName('exchange_rate').Value := json.GetValue('exchange_rate').Value;
cmd.Parameters.ParamByName('exchange_rate_usd').Value := json.GetValue('exchange_rate_usd').Value;
cmd.Parameters.ParamByName('name').Value := json.GetValue('name').Value;
// 处理 users 数组
users := json.GetValue('users') as TJSONArray;
for i := 0 to users.Count - 1 do begin
user := users.Items[i] as TJSONObject;
cmd.Parameters.ParamByName('users_rate').Value := user.GetValue('rate').Value;
cmd.Parameters.ParamByName('users_user_id').Value := user.GetValue('user_id').Value;
cmd.Execute;
end;
// 处理 departments 数组
departments := json.GetValue('departments_info') as TJSONArray;
for i := 0 to departments.Count - 1 do begin
department := departments.Items[i] as TJSONObject;
// 从 JSON 对象中获取部门信息
department_id := department.GetValue('department_id').Value;
rate := department.GetValue('rate').Value;
name := department.GetValue('name').Value;
// 将部门信息插入到数据库中
query.SQL.Clear;
query.SQL.Add('INSERT INTO departments (order_id, department_id, rate, name) VALUES (:order_id, :department_id, :rate, :name)');
query.ParamByName('order_id').AsInteger := order_id;
query.ParamByName('department_id').AsString := department_id;
query.ParamByName('rate').AsString := rate;
query.ParamByName('name').AsString := name;
query.ExecSQL;
end;
上面的代码会将部门信息插入到一个名为 departments 的表中，该表包含以下列：
order_id：订单 ID，用于关联订单和部门信息。
department_id：部门 ID。
rate：占比。
name：部门名称。
需要根据实际情况调整表结构和 SQL 语句。
以下答案由GPT-3.5大模型与博主波罗歌共同编写：
首先，需要将该JSON数组解析为Delphi中的TJSONObject对象，你可以使用Delphi所自带的System.JSON单元来进行操作。
可以使用以下代码来载入JSON：
uses System.JSON;
var
jsonStr: string;
jsonObj: TJSONObject;
begin
jsonStr := '{"order_id": 123, "order_no": "535"}';
jsonObj := TJSONObject.ParseJSONValue(jsonStr) as TJSONObject;
try
// ...
finally
jsonObj.Free;
end;
end;
接下来，你需要将其保存到MSSQL数据库。可以使用以下步骤进行操作：
Microsoft ADOx.x Object Library组件。uses ADODB;
var
conn: TADOConnection;
begin
conn := TADOConnection.Create(nil);
conn.ConnectionString := 'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=yourDbName;Data Source=yourDbServerName';
conn.LoginPrompt := False;
conn.Open;
try
// ...
finally
conn.Free;
end;
end;
uses ADODB, System.JSON;
var
conn: TADOConnection;
cmd: TADOCommand;
jsonStr: string;
jsonObj: TJSONObject;
begin
conn := TADOConnection.Create(nil);
cmd := TADOCommand.Create(nil);
conn.ConnectionString := 'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=yourDbName;Data Source=yourDbServerName';
conn.LoginPrompt := False;
conn.Open;
jsonStr := '{"order_id": 123, "order_no": "535"}';
jsonObj := TJSONObject.ParseJSONValue(jsonStr) as TJSONObject;
try
cmd.CommandText := 'INSERT INTO yourTableName (json_field) VALUES (:jsonParam)';
cmd.CommandType := cmdText;
cmd.Prepared := True;
cmd.Parameters.ParamByName('jsonParam').DataType := ftString;
cmd.Parameters.ParamByName('jsonParam').Size := Length(jsonStr);
cmd.Parameters.ParamByName('jsonParam').Value := jsonStr;
cmd.Execute;
finally
jsonObj.Free;
cmd.Free;
conn.Free;
end;
end;
以上就是将 JSON 数组保存到 MSSQL 数据库的大致方法。
当然，这只是一个简单的示例。在具体实现中，你可能需要针对业务需求进行更复杂的处理。
如果我的回答解决了您的问题，请采纳！
支持json类型的存储吧