How to store and retrieve nested JSON array in laravel 6

Question

I am trying to retrieve the data saved as json in mysql. My migration looks like below:

Schema::create('items', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->string('name')->nullable();
        $table->json('add_ons')->nullable();
        $table->timestamps();
    });

I have tried saving the below JSON from postman and it saved perfectly.

{
    "itemName": "chicken",
    "addons": {
        "required": false,
        "min": 2,
        "max": 3,
        "data": [
            {
                "name": "sauces",
                "type": [
                    {
                        "name": "white sauce",
                        "type": [
                            {
                                "name": "whiteOne",
                                "price": 10
                            },
                            {
                                "name": "whiteTwo",
                                "price": 20
                            }
                        ]
                    },
                    {
                        "name": "red sauce",
                        "price": 10
                    }
                ]
            }
        ]
    }
}

Now, I trying to retrieve the price of 'whiteOne' under 'white sauce' and getting nothing other than null or laravel error response.

I have tried

Item::whereJsonContains('add_ons->data->name','sauces')->get()
Item::whereJsonContains('add_ons->data->name','sauces')->find(16)

After saving the 'add_ons' column has below data:

{
  "required": false,
  "min": 2,
  "max": 3,
  "data": [
    {
      "name": "sauces",
      "type": [
        {
          "name": "white sauce",
          "type": [
            {
              "name": "whiteOne",
              "price": 10
            },
            {
              "name": "whiteTwo",
              "price": 20
            }
          ]
        },
        {
          "name": "red sauce",
          "price": 10
        }
      ]
    }
  ]
}

Can anyone help me ?

I think it is typo in add_ons->data - it has to be add_ons->addons->data — Robin Gillitzer
– Robin Gillitzer, Commented Feb 19, 2020 at 10:42
Thanks for rectifying one of the issues. Tried your suggestion but it is not working.@RobinGillitzer — Debu
– Debu, Commented Feb 19, 2020 at 10:50
What do you get with these: Item::whereJsonContains('add_ons->itemName','chicken')->get(); — Robin Gillitzer
– Robin Gillitzer, Commented Feb 19, 2020 at 10:56
Illuminate\Database\Eloquent\Collection {#306 ▼ #items: [] } @RobinGillitzer — Debu
– Debu, Commented Feb 19, 2020 at 10:59

Debu · Accepted Answer · 2020-02-20 11:35:21Z

I used this function to get the value of the index.

public function search($array, $key, $value) { 
        // RecursiveArrayIterator to traverse an 
        // unknown amount of sub arrays within 
        // the outer array. 
        $arrIt = new \RecursiveArrayIterator($array); 

        // RecursiveIteratorIterator used to iterate 
        // through recursive iterators 
        $it = new \RecursiveIteratorIterator($arrIt); 

        foreach ($it as $sub) { 

            // Current active sub iterator 
            $subArray = $it->getSubIterator(); 

            if ($subArray[$key] === $value) { 
                //$result[] = iterator_to_array($subArray); 
                $result = iterator_to_array($subArray); 
             } 
        } 
        return $result; 
    }

Now when I pass the values in the function I get the appropriate values.

$item = Item::where('id','=',16)->first();
$res = $this->search($item->add_ons['data'], 'name', 'whiteTwo'); 
echo $res["name"]." - ".$res['price'] ."<br>";

Collectives™ on Stack Overflow

How to store and retrieve nested JSON array in laravel 6

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related