Laravel - Creating an object from a JSON array to save it in a SQL database

Question

What I am trying to do is to send a JSON array (that was gotten from Guzzle) to my SQL database. I have gotten to the point where I am able to get the response and display the gotten JSON array on a webpage. The array is defined as the $data variable. The $data variable gets decoded using this:

$data = json_decode($response->getBody()->getContents());

This is able to get the JSON and decode it with no problem. The part I am stuck on is taking the $data variable, processing it and sending it to my database. From what I understand is that you are required to convert the JSON into an array and then send it to the database.

The JSON format is like this:

[{
  "INTLDES": "2017-042Z",
  "NORAD_CAT_ID": "42848",
  "OBJECT_TYPE": "TBA",
  "SATNAME": "OBJECT Z",
  "COUNTRY": "TBD",
  "LAUNCH": "2017-07-14",
  "SITE": "TTMTR",
  "DECAY": null,
  "PERIOD": "96.52",
  "INCLINATION": "97.61",
  "APOGEE": "597",
  "PERIGEE": "586",
  "COMMENT": null,
  "COMMENTCODE": null,
  "RCSVALUE": "0",
  "RCS_SIZE": null,
  "FILE": "6242",
  "LAUNCH_YEAR": "2017",
  "LAUNCH_NUM": "42",
  "LAUNCH_PIECE": "Z",
  "CURRENT": "Y",
  "OBJECT_NAME": "OBJECT Z",
  "OBJECT_ID": "2017-042Z",
  "OBJECT_NUMBER": "42848"
}]

My Satellite Model goes like this:

protected $fillable = [
    'intldes',
    'norad_cat_id',
    'object_type',
    'satname',
    'country',
    'launch',
    'site',
    'decay',
    'period',
    'inclination',
    'apogee',
    'perigee',
    'comment',
    'commentcode',
    'rcsvalue',
    'rcs_size',
    'file',
    'launch_year',
    'launch_num',
    'launch_piece',
    'current',
    'object_name',
    'object_id',
    'object_number'
];

My migrations file:

Schema::create('satellites', function (Blueprint $table) {
    $table->increments('id');
    $table->string('intldes');
    $table->string('norad_cat_id');
    $table->string('object_type');
    $table->string('satname');
    $table->string('country');
    $table->string('launch')->nullable();
    $table->string('site')->nullable();
    $table->string('decay')->nullable();
    $table->string('period')->nullable();
    $table->string('inclination')->nullable();
    $table->string('apogee')->nullable();
    $table->string('perigee')->nullable();
    $table->string('comment')->nullable();
    $table->string('commentcode')->nullable();
    $table->string('rcsvalue')->nullable();
    $table->string('rcs_size')->nullable();
    $table->string('file')->nullable();
    $table->string('launch_year')->nullable();
    $table->string('launch_num')->nullable();
    $table->string('launch_piece')->nullable();
    $table->string('current')->nullable();
    $table->string('object_name');
    $table->string('object_id');
    $table->string('object_number');
    $table->timestamps();
});

I tried making an $object array, which did not work.

TL;DR: I want to take the $data variable, which contains the decoded JSON and create something that allows it to get saved into my 'satellites' SQL database.

EDIT: Here is the full Satellite controller:

    public function displayer(){
    $api = new Client([
    'base_uri' => 'https://www.space-track.org',
    'cookies' => true, 
    ]); $api->post('ajaxauth/login', [
      'form_params' => [
         'identity' => '#', 
         'password' => '#', 
     ],
    ]);
    $response = $api->get('basicspacedata/query/class/satcat/orderby/INTLDES%20desc/limit/5/metadata/false');
    $data = json_decode($response->getBody()->getContents(), true);
    $data = array_change_key_case($data, CASE_LOWER);
    $model = Satellite::create($data);
    dd($data);
}

This may not be relevant to the question, but that long array of $fillable can be changed to be protected $guarded = ['id']; (instead) blacklisting only id while others become mass assignable. — omitobi
– omitobi, Commented Jul 27, 2017 at 22:35
You don't have to edit actually, just for anyone who finds it useful, your code is already okay — omitobi
– omitobi, Commented Jul 27, 2017 at 22:44

fubar · Accepted Answer · 2017-07-27 22:53:44Z

2

It looks like your JSON key names match up nicely with your model attributes, with the exception of being capitalised.

Try mapping the data keys to lowercase and then creating your model instance. Per @OmisakinOluwatobi suggestion, you can use pass true to json_decode to retrieve the data as an array.

Edit - I missed that your response data was an array of objects. The following update will iterate over the response data and create a new Satellite for each.

// Retrieve data from response
$data = json_decode($response->getBody()->getContents(), true);

// Iterate over response data
foreach ($data as $attributes) {
    // Change attribute keys to lowercase
    $attributes = array_change_key_case($attributes, CASE_LOWER);

    // Create satellite model
    Satellite::create($attributes);
}

edited Jul 27, 2017 at 22:53

answered Jul 27, 2017 at 22:26

fubar

17.4k4 gold badges43 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

omitobi Over a year ago

how about json_decode($content, true) to return array?

fubar Over a year ago

@OmisakinOluwatobi - excellent suggestion. I'd forgotten about addressing the object -> array part of the question.

D. 777KLM Over a year ago

Thanks for your responses, I think this is the code I need. but I am getting an error - Call to undefined method App\Http\Controllers\Satellite::create(). I think I am forgetting something.

omitobi Over a year ago

use App\Satellite; thats what is missing @D.777KLM

D. 777KLM Over a year ago

@OmisakinOluwatobi That fixed it but now I get an error that states must be of the type array, string given. It seems like to does not convert to a array. I added the true as you suggested.

|

Mwatha Kinyua · Accepted Answer · 2017-10-28 14:31:26Z

0

It is actually as simple as $encoded = json_encode($request->your_array);. This $encoded will now be "savable" to sql database. When you later access the encoded data, you can use a JSON parser to convert back to a json array. Example using jQuery var your_array = $.parseJSON($response.body);

answered Oct 28, 2017 at 14:31

Mwatha Kinyua

437 bronze badges

Collectives™ on Stack Overflow

Laravel - Creating an object from a JSON array to save it in a SQL database

2 Answers 2

12 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related