Save nested json into database in laravel

Question

I'm consuming an API with Guzzle via laravel and storing the response into a database.

I've been successful in storing the "ratings" part of the JSON response. But now I'd like to add "meta" - "extractDate".

What do I need to change to get this to work?

Below is the JSON API response I'm working from and part of the controller I'm using to save into my database.

Using laravel 7.0

**JSON from API**

{
    "ratings": [
        {
            "ratingId": 12,
            "ratingName": "5",
            "ratingKey": "fhrs_5_en-gb",
            "ratingKeyName": "5",
            "schemeTypeId": 1,
            "links": [
                {
                    "rel": "self",
                    "href": "http://api.ratings.food.gov.uk/ratings/12"
                }
            ]
        }
    ],
    "meta": {
        "dataSource": "API",
        "extractDate": "2020-06-29T21:24:43.820107+01:00",
        "itemCount": 11,
        "returncode": "OK",
        "totalCount": 11,
        "totalPages": 1,
        "pageSize": 11,
        "pageNumber": 1
    }
}

**controller snippet**

        $client = $this->client();
        $request = $client->request('GET', 'https://api.ratings.food.gov.uk/ratings');
        $ratings = json_decode($request->getBody()->getContents(), true);

        collect($ratings['ratings'])
            ->each(function($rating, $key) {
                Rating::updateOrCreate([
                    'ratingId' => $rating['ratingId'],
                    'ratingName' => $rating['ratingName'],
                    'ratingKey' => $rating['ratingKey'],
                    'ratingKeyName' => $rating['ratingKeyName'],
                    'schemeTypeId' => $rating['schemeTypeId'],
                    'extractDate' => $rating['extractDate']
                ]);
            });

Edit -- Additional Info

Correct me if I'm wrong but at the moment I think the collection is only able to save the contents of the "Ratings" object. I've tried to remove the object from the collect line and amend each line as:

 'ratingId' => $rating['rating']['ratingId'],
 'extractDate' => $rating['meta']['extractDate']

But I get 'Undefined index: rating'

DD of $ratings

array:3 [▼
  "ratings" => array:11 [▼
    0 => array:6 [▼
      "ratingId" => 12
      "ratingName" => "5"
      "ratingKey" => "fhrs_5_en-gb"
      "ratingKeyName" => "5"
      "schemeTypeId" => 1
      "links" => array:1 [▶]
    ]
    1 => array:6 [▶]
    2 => array:6 [▶]
    3 => array:6 [▶]
    4 => array:6 [▶]
    5 => array:6 [▶]
    6 => array:6 [▶]
    7 => array:6 [▶]
    8 => array:6 [▶]
    9 => array:6 [▶]
    10 => array:6 [▶]
  ]
  "meta" => array:8 [▼
    "dataSource" => "API"
    "extractDate" => "2020-06-29T22:56:04.250649+01:00"
    "itemCount" => 11
    "returncode" => "OK"
    "totalCount" => 11
    "totalPages" => 1
    "pageSize" => 11
    "pageNumber" => 1
  ]
  "links" => array:1 [▶]
]

You need to post more information. What specifically is not working? Error messages, supporting information, things you have tried. — Kurt Friars
$ratings = json_decode($request->getBody()->getContents(), true); — SteveO
Can you dd($ratings), also try using a foreach loop instead of collect, if my hunch is correct. — Kurt Friars

Kurt Friars Kurt Friars · Accepted Answer · 2020-06-29T22:04:49

You don't have any data structured like this:

'ratingId' => $rating['rating']['ratingId']

There is no key in your that is 'rating'.

You have

'ratingId' => $ratings['ratings'][$idx]['rating_id']

I think what you want is this:

collect($ratings['ratings'])
    ->each(function($rating, $key) use ($ratings) {
        Rating::updateOrCreate([
            'ratingId' => $rating['ratingId'],
            'ratingName' => $rating['ratingName'],
            'ratingKey' => $rating['ratingKey'],
            'ratingKeyName' => $rating['ratingKeyName'],
            'schemeTypeId' => $rating['schemeTypeId'],
            'extractDate' => $ratings['meta']['extractDate']
        ]);
    });

My original comment is based on your question :)

Save nested json into database in laravel

1 Answers