asked on

Best way to store JSON Response into Laravel and extract

HI,
I have a webservice that when I provide a StyleID for a vehicle it returns urls of vehicle pics and their associated colors etc in json like below (basically 3 categories, 'view','colorized' and 'stock')

I'm storing each category into a separate column in my DB with column type set to JSON (seemed to make sense). So then at a later point I can retrieve an image set for a vehicle without having to call the API over and over as sometimes the page can contain a number of vehicles and calling to the service over and over for one page is way too slow. So populating the table would happen after hours.

This way I just reference the StyleID in the table in my DB and retrieve the correct urls by passing a few parameters.

So trying to retrieve colorized images for a car for example with parameters like:
@colorCode: PBJ,
@width: 320
@shotCode: 01
@backgroundDescription: White

{
   "view":[
      {
         "@width":"320",
         "@height":"240",
         "@shotCode":"07",
         "@backgroundDescription":"White",
         "@href":"http://media.chromedata.com/MediaGallery/media/Mjc0ODgyXk1lZGlhIEdhbGxlcnk/5zalboWvMJfiO8memsH19dndmAek1JSv6veHqtcd9-hMO4PAO3xwZa7RIeSA2iqayIk7XPB183yKmimK_3GFSgWF25TrWriygD-_AL3sncFE6i7EWXaNzhd4FGV8Euh4xSo7mEENJFg/2021JES040022_320_07.jpg",
         "@type":"resource",
         "flags":{
            "carryOver":"Y",
            "exactMatch":"N",
            "oemTemp":"N"
         }
      },
      {
         "@width":"1280",
         "@height":"960",
         "@shotCode":"06",
         "@backgroundDescription":"White",
         "@href":"http://media.chromedata.com/MediaGallery/media/Mjc0ODgyXk1lZGlhIEdhbGxlcnk/5zalboWvMJfiO8memsH19dndmAek1JSv6veHqtcd9-g5fTrF109TZp1SCNQ0fU4c9KYrBbTR8dWP9-2mGl0V9aGkHWPWep6VSZcFWS9hQJ7CaKPnM4QsHzQ67D2yopmQ4pd_dHa_bleDoDohyRWtIg/2021JES040021_1280_06.jpg",
         "@type":"resource",
         "flags":{
            "carryOver":"Y",
            "exactMatch":"N",
            "oemTemp":"N"
         }
      },
      {
         "@width":"2100",
         "@height":"1575",
         "@shotCode":"11",
         "@backgroundDescription":"Transparent",
         "@href":"http://media.chromedata.com/MediaGallery/media/Mjc0ODgyXk1lZGlhIEdhbGxlcnk/5zalboWvMJfiO8memsH19dndmAek1JSv6veHqtcd9-iLHw8LnwGFNvt8alURtJ4Oc3JDs-tEzdLSYcjipUFzV-Zb2wG0e2VsCbw-DrhSdF9Wk8lzxZPiyXcZyHXQOyb7ZZbnoZe9mcdGNmlswUiQ1_Rp-hGRvTsF/2021JES040023_2100_11.png",
         "@type":"resource",
         "flags":{
            "carryOver":"Y",
            "exactMatch":"N",
            "oemTemp":"N"
         }
      },
      {
         "@width":"320",
         "@height":"240",
         "@shotCode":"44",
         "@backgroundDescription":"White",
         "@href":"http://media.chromedata.com/MediaGallery/media/Mjc0ODgyXk1lZGlhIEdhbGxlcnk/5zalboWvMJfiO8memsH19dndmAek1JSv6veHqtcd9-glE_mzMMtnlthSpd2HrFNavB2UshXp406YrWnED59UkaTJaub3Kzkg90UgLqaIVXtAun0ihR88sRaU94imJG0qonf8XCaf6W8/2021JES040033_320_44.jpg",
         "@type":"resource",
         "flags":{
            "carryOver":"Y",
            "exactMatch":"N",
            "oemTemp":"N"
         }
      }
   ],
   "colorized":[
      {
         "@primaryColorOptionCode":"PT6",
         "@secondaryColorOptionCode":"",
         "@primaryRGBHexCode":"7E7267",
         "@secondaryRGBHexCode":"",
         "@width":"1280",
         "@height":"960",
         "@shotCode":"01",
         "@backgroundDescription":"White",
         "@href":"http://media.chromedata.com/MediaGallery/media/Mjc0ODgyXk1lZGlhIEdhbGxlcnk/4QRlU77m-R0m0i5MmJdZ91qENP8mrd3gZwyQlUjYAERJglsXT0rS7Gc4fwcl1zKr0Ip5Z4rDXebna7BhxHqPUzZqcmGnlGzYiCWVuC_BGB2pTOHRx6TAp1P5ucuIexaRwyfBCxLtQ9QekK3WQuYS1An9fXu3EkWdBmSiz28M1io6yKhN76TO8w/cc_2021JES040029_01_1280_PT6.jpg",
         "@type":"resource",
         "flags":{
            "carryOver":"Y",
            "exactMatch":"N",
            "oemTemp":"N"
         }
      },
      {
         "@primaryColorOptionCode":"PF2",
         "@secondaryColorOptionCode":"",
         "@primaryRGBHexCode":"DA3421",
         "@secondaryRGBHexCode":"",
         "@width":"1280",
         "@height":"960",
         "@shotCode":"01",
         "@backgroundDescription":"White",
         "@href":"http://media.chromedata.com/MediaGallery/media/Mjc0ODgyXk1lZGlhIEdhbGxlcnk/4QRlU77m-R0m0i5MmJdZ91qENP8mrd3gZwyQlUjYAERJglsXT0rS7MQV1Iwi5cqA-jlTTGzFU3UYKuTr6YFHiMbVLVSibTUJJlOU0-S0In_I_n-TPP911cUCc68J0biTOk2GnJqjr6yk8YCMwNqCRc_O9TV562AR0SSP3ndtAnkMKcvXCIBrow/cc_2021JES040022_01_1280_PF2.jpg",
         "@type":"resource",
         "flags":{
            "carryOver":"Y",
            "exactMatch":"N",
            "oemTemp":"N"
         }
      },
      {
         "@primaryColorOptionCode":"PBJ",
         "@secondaryColorOptionCode":"",
         "@primaryRGBHexCode":"0A4B71",
         "@secondaryRGBHexCode":"",
         "@width":"1280",
         "@height":"960",
         "@shotCode":"01",
         "@backgroundDescription":"White",
         "@href":"http://media.chromedata.com/MediaGallery/media/Mjc0ODgyXk1lZGlhIEdhbGxlcnk/4QRlU77m-R0m0i5MmJdZ91qENP8mrd3gZwyQlUjYAERJglsXT0rS7JD4oZsX0y7q87n2LxPIxIq-neWckXBVALRr5ITZI9S75mkxja09DcTo6fXXpcrMx6MwFeUVVDpouE4acqDenLExY5QYlhacoLITLWyB_GxpIxKixDag1OCusmFc_oRpxA/cc_2021JES040020_01_1280_PBJ.jpg",
         "@type":"resource",
         "flags":{
            "carryOver":"Y",
            "exactMatch":"N",
            "oemTemp":"N"
         }
      },
      {
         "@primaryColorOptionCode":"PSC",
         "@secondaryColorOptionCode":"",
         "@primaryRGBHexCode":"838587",
         "@secondaryRGBHexCode":"",
         "@width":"640",
         "@height":"480",
         "@shotCode":"01",
         "@backgroundDescription":"White",
         "@href":"http://media.chromedata.com/MediaGallery/media/Mjc0ODgyXk1lZGlhIEdhbGxlcnk/4QRlU77m-R0m0i5MmJdZ91qENP8mrd3gZwyQlUjYAERJglsXT0rS7C3nO6K5OcyNenDumnvK1gziLanw3J1DBUtl_QYmNDirU2YSzBV5QfiM07ZNnpyx42RooUX-trK1-cQRna4TNUuBvkRK5XcOvmntNFFGkIAg-HGL3koYmqAIcOCyKEZWsQ/cc_2021JES040017_01_640_PSC.jpg",
         "@type":"resource",
         "flags":{
            "carryOver":"Y",
            "exactMatch":"N",
            "oemTemp":"N"
         }
     }
   ],
   "stock":{
      "@width":"400",
      "@height":"200",
      "@href":"http://media.chromedata.com/MediaGallery/media/Mjc0ODgyXk1lZGlhIEdhbGxlcnk/GNd_ulFNA8wZtnAHSi29W8RZ4ggBc0FVBBjh_R7VizwR3TNfdpc3RdTdi7G0p5m3FdjcyeMxiLsGK9JKngUjpifideAslXPd/32445.jpg",
      "@type":"resource",
      "flags":{
         "carryOver":"N",
         "exactMatch":"N",
         "oemTemp":"Y"
      }
   }
}

Open in new window

So I'm stuck on :
1) is json column the right/best way to store?
2) how to retrieve a certain pic url using some parameters in laravel controller?

tjyoung

ASKER

thiking something like:

$pics = DB::table('vehicles_styles')
->where('StyleID',$vehicle->StyleID)
->whereJsonContains('Colorized', ['@primaryColorOptionCode' => $vehicle->ColorCode])
->first();

tjyoung

ASKER

It returned:

{#1513 ▼
  +"id": 74
  +"StyleID": "417769"
  +"View": "[{"@href": "https://media.chromedata.com/MediaGallery/media/Mjc0ODgyXk1lZGlhIEdhbGxlcnk/P5QV-UOFBs3Q0TMEjsoA4P_xeBOHVAWz7MB-Bdu80HOxSMiezagkKX_TYFq7-lKnVPkdcnPZ ▶"
  +"Colorized": "[{"@href": "https://media.chromedata.com/MediaGallery/media/Mjc0ODgyXk1lZGlhIEdhbGxlcnk/ITzwzHywyp_xIXseBTH6ZfC_9HNT8RX5vHtbhaOlYWaRW62qc5ngjynr9kLJu2MSI1emKPjE ▶"
  +"Stock": "{"@href": "https://media.chromedata.com/MediaGallery/media/Mjc0ODgyXk1lZGlhIEdhbGxlcnk/0RH2WYLL1tcIwbejS3KOZPaX7UCHXXlwJA8UfSwVW2TA-hLLEBHc3oMY1-nyP5kvRrALuXiY0 ▶"
  +"created_at": "2020-11-04 17:18:46"
  +"updated_at": "2020-11-04 17:18:46"
}

Open in new window

tjyoung

ASKER

Above just returned the styleID with all the fields. Not that great.
Ignored my jsonContains stuff

Chris Stanyon

Hey there,

The JSON column may be the right type, depending on your needs. If this JSON has a one-to-one relationship with the vehicle, and you only ever want to search the vehicle's specific JSON for the images, then it will probably be fine. You'll likely want to add some methods to your Vehicle model (such as an Accessor property) to retrieve and parse the JSON into an object. You can then filter (using the Collection filter / where / first as previously discussed). Let's assume you have added a property to your Vehicle model (and added it to the database migration), and let's assume you've called that column image_data

class Vehicle extends Model
{
    public function getImageDataAttribute()
    {
        return json_decode($this->image_data);
    }
}

Open in new window

Now you can use the data like any property on your Model:

$car = Vehicle::find(123); // get the Vehicle with ID of 123
$images = $car->ImageData;

Open in new window

That will set $images to an Object containing your 3 properties - view, colorized & stock. Each of those properties contains an array of data, so you can filter them according to your needs

tjyoung

ASKER

great taking a look at this now! Thanks

tjyoung

ASKER

HI,
So my vehicle may or may not have images for it already. So in my existing code I have a table vehicles with the column ImageList that contains the urls from a 3rd party if present. So I"m checking for images first and if no pics, I'm querying the vehicles_styles table.

if($vehicle->ImageList[0] == NULL)
                {

                    $pics = DB::table('vehicles_styles')
                    ->select('Colorized')
                    ->where('StyleID',$vehicle->StyleID)
                    ->first();


                    if($pics)
                    {
                        $images = json_decode($pics->Colorized);
                        dd($images);
                        exit();

                    }


                }

Open in new window

with above I'm getting this:

{#655 ▼
  +"@href": "https://media.chromedata.com/MediaGallery/media/Mjc0ODgyXk1lZGlhIEdhbGxlcnk/ITzwzHywyp_xIXseBTH6ZfC_9HNT8RX5vHtbhaOlYWaRW62qc5ngjynr9kLJu2MSI1emKPjEGfOM9ki0LwTM ▶"
  +"@type": "resource"
  +"flags": {#1359 ▶}
  +"@width": "1280"
  +"@height": "960"
  +"@shotCode": "03"
  +"@primaryRGBHexCode": "B1B2AF"
  +"@secondaryRGBHexCode": ""
  +"@backgroundDescription": "Transparent"
  +"@primaryColorOptionCode": "3H"
  +"@secondaryColorOptionCode": ""
}

Open in new window

So is there a way I can add to my code above to filter those $images results for specific set in 'Colorized' column? I don't think 'filter' works with query builder etc.

tjyoung

ASKER

$pics = DB::table('vehicles_styles')
                    ->select('Colorized')
                    ->where('StyleID',$vehicle->StyleID)
                    ->get();

Open in new window

I could use 'get' and retrieve them all

tjyoung

ASKER

This seems to have promise. Getting the correct response at least:

if($vehicle->ImageList[0] == NULL)
{

$pics = DB::table('vehicles_styles')
->select('Colorized')
->where('StyleID',$vehicle->StyleID)
->first();

if($pics)
{
$images = json_decode($pics->Colorized);
$collection = collect($images);
$collection = $collection
->where('@primaryColorOptionCode',$vehicle->ColorCode)
->where('@width','640')
->where('@backgroundDescription','White')
->where('@shotCode',03);
dd($collection);
exit();

}

}

ASKER CERTIFIED SOLUTION

Chris Stanyon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial