How to import a json column

[lucasaguiar86]

I'm trying to import a file that contains json data in some columns and this data needs to be imported into JSONB fields in PostgreSQL.

Json data example:
{"phone":"6365615298", "website":"http://www.happychinafood.com"}

However, when the file gets imported, the data imported appears as follows in the database:
"{\""phone\"":\""6365615298\"", \""website\"":\""http://www.happychinafood.com\""}"

I need the data imported EXACTLY as how the example is provided.

Is there any way to achieve this?

[lucasaguiar86]

Found the solution, basically, the following things need to be considered:

• The field containing JSON data needs to be decoded
• The model should cast these fields as an array

Laravel models convert data to arrays to insertion, but if the fields in DB are JSON fields the model will automatically serialize the values

Reference: https://laravel.com/docs/8.x/eloquent-mutators#array-and-json-casting

Example of my code:

/**
* @throws \JsonException
*/
public function model(array $row)
{
$this->affectedRows++;
$parameters = [];
$i = 0;
foreach ($this->columns as $column => $value) {
if ($value) {
$parameters[$column] = $value;
} elseif ($this->isJson($row[$i])) {
$parameters[$column] = json_decode($row[$i], true, 64, JSON_THROW_ON_ERROR);
} else {
$parameters[$column] = $row[$i];
}
$i++;
}
return new $this->modelClass($parameters);
}

private function isJson($string): bool
{
json_decode($string);
return json_last_error() === JSON_ERROR_NONE;
}`