How to import a JSON column in Laravel Excel

Question

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?

The package I'm using is maatwebsite/excel

Answer 1

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:

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;
}