使用Laravel将JSON文件上传到MYSQL不起作用

Question

我正在编写一个脚本，使用laravel将JSON文件和我们以前的CRM系统中的所有公司数据上传到MYSQL数据库中，我在解析JSON数据时遇到了问题。我已经将其转换为数组，并运行foreach来上传每个公司，但它抛出了一个错误，说它无法从数组转换为字符串。我不确定我在这里做错了什么，我的代码在下面，还有一家公司(都很相似(：

public function uploadCompanies()
{
$json = File::get("database/data/accounts.json");
$data = json_decode($json);
foreach($data as $item) {
DB::table('ucustomers')->insert(
[
'dynamics_guid'         => $item->accountid,
'customer_code'         => $item->mc_unleashedcode,
'customer_name'         => $item->name,
'phone_number'          => $item->telephone1,
'fax_number'            => $item->fax,
'email'                 => $item->emailaddress1,
'website'               => $item->websiteurl,
'override_reps'         => [],
'updated_at'            => date('Y-m-d H:i')
]
);
}
}

accounts.json

[
{
"@odata.etag": "W/"145746454"",
"name": "Core Pharmacy",
"opendeals": 0,
"modifiedon": "2020-08-29T14:11:19Z",
"address1_stateorprovince": "VIC",
"telephone1": "03 9338 1504",
"accountid": "5b46f158-d8ef-e911-a812-000d3a799888",
"websiteurl": null,
"mc_unleashedcode": "C1000096",
"fax": "03 9334 5030"
"emailaddress1": "[e-mail]",
}
]

错误

ErrorException 
Array to string conversion
at vendor/laravel/framework/src/Illuminate/Support/Str.php:488
484| 
485|         $result = array_shift($segments);
486| 
487|         foreach ($segments as $segment) {
> 488|             $result .= (array_shift($replace) ?? $search).$segment;
489|         }
490| 
491|         return $result;
492|     }
+8 vendor frames 
9   app/Unleashed/DynamicsUpload.php:52
IlluminateDatabaseQueryBuilder::insert()
10  app/Console/Commands/InsertDynamicsData.php:41
AppUnleashedDynamicsUpload::uploadCompanies()

Answer 1

您的问题是。当插入时，您试图将数组分配给字符串

public function uploadCompanies()
{
$json = File::get("database/data/accounts.json");
$data = json_decode($json);
foreach($data as $item) {
DB::table('ucustomers')->insert(
[
'dynamics_guid'         => $item->accountid,
'customer_code'         => $item->mc_unleashedcode,
'customer_name'         => $item->name,
'phone_number'          => $item->telephone1,
'fax_number'            => $item->fax,
'email'                 => $item->emailaddress1,
'website'               => $item->websiteurl,
'override_reps'         => [], // error on this one
'updated_at'            => date('Y-m-d H:i')
]
);
}
}

有两种解决方案可以修复它。

如果你想直接将数组插入到表中，你需要像下面这样编码：

public function uploadCompanies()
{
$json = File::get("database/data/accounts.json");
$data = json_decode($json);
foreach($data as $item) {
DB::table('ucustomers')->insert(
[
'dynamics_guid'         => $item->accountid,
'customer_code'         => $item->mc_unleashedcode,
'customer_name'         => $item->name,
'phone_number'          => $item->telephone1,
'fax_number'            => $item->fax,
'email'                 => $item->emailaddress1,
'website'               => $item->websiteurl,
'override_reps'         => json_encode([]), // need to encode first
'updated_at'            => date('Y-m-d H:i')
]
);
}
}

解决方案2是使用创建模型：

但首先需要将overridereps字段强制转换为数组。所以laravel会帮你处理

// assume you model name Customer
class Customer extends Model
{
protected $casts =[
'override_reps'=>'array',
];
}
// on controller
public function uploadCompanies()
{
$json = File::get("database/data/accounts.json");
$data = json_decode($json);
foreach($data as $item) {
Customer::create(
[
'dynamics_guid'         => $item->accountid,
'customer_code'         => $item->mc_unleashedcode,
'customer_name'         => $item->name,
'phone_number'          => $item->telephone1,
'fax_number'            => $item->fax,
'email'                 => $item->emailaddress1,
'website'               => $item->websiteurl,
'override_reps'         => [],//lavavel model will encode it before insert
'updated_at'            => date('Y-m-d H:i')
]
);
}
}