Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

Corbin's avatar

Structuring the SQL JSON data type and adding new key values to the output using an api resource?

I'm trying to add a label that is matched to an array on my records model and an is_picked: false to the json emotions collection in an API resource:

** Example Current Output**

"data":[
   {
      "id":91,
      "title":"Temporibus sed ut voluptas nesciunt.",
      "emotions":[
         {
            "intensity":10,
            "emotion_id":1,
            "newIntensity":3
         },
         {
            "intensity":10,
            "emotion_id":2,
            "newIntensity":3
         }
      ]
   },
   {
      "id":92,
      "title":"Facilis provident qui tempore sit illum fuga incidunt odio.",
      "emotions":[
         {
            "intensity":10,
            "emotion_id":1,
            "newIntensity":3
         },
         {
            "intensity":10,
            "emotion_id":2,
            "newIntensity":3
         }
      ]
   }
]

Desired Output Example

"data":[
   {
      "id":91,
      "title":"Temporibus sed ut voluptas nesciunt.",
      "emotions":[
         {
            "intensity":10,
            "emotion_id":1,
            "newIntensity":3
            "label": "Happy",
            "is_picked": false
         },
         {
            "intensity":10,
            "emotion_id":2,
            "newIntensity":3
            "label": "Sad",
            "is_picked": false
         }
      ]
   },
   {
      "id":92,
      "title":"Facilis provident qui tempore sit illum fuga incidunt odio.",
      "emotions":[
         {
            "intensity":10,
            "emotion_id":1,
            "newIntensity":3
            "label": "Happy",
            "is_picked": false
         },
         {
            "intensity":10,
            "emotion_id":2,
            "newIntensity":3
            "label": "Sad",
            "is_picked": false
         }
      ]
   }
]

My Record Table

Schema::create('records', function (Blueprint $table) {
    $table->id();
    $table->text('title')->nullable();
    $table->json('emotions')->nullable();
    $table->timestamps();
});

Model

class Record extends Model
{
    use HasFactory;

    protected $casts = [
        'emotions' => 'json',
    ];

    public $emtionsLabels = ['Happy', 'Sad'];
}

I was able to structure the JSON output for the emotions to be closer to the Desired Output Example in the Controller:

class RecordController extends Controller
{
    public function index()
    {

        $emotions = Record::first()->emotions;

        $phpRecords = json_decode($emotions, true);

        $newEmotions;
        $record = new Record();
        $labels = $record->emtionsLabels;
        foreach($phpRecords as $emotion){
            $newEmotions[] = 
                [
                    'intensity' => $emotion['intensity'],
                    'new_intensity' => $emotion['newIntensity'],
                    'emotion_id'=> $emotion['emotion_id'],
                    'label' => $labels[$emotion['emotion_id'] - 1]
		    'is_picked' => false
                ];
        }

        return collect($newEmotions);

    }
}

Now what I'm trying to figure out is how to format that json output in my API RecordResource, or Record model, to be the similar to the code in my controller example:

Current RecordResource

class RecordResource extends JsonResource
{
    public function toArray($request)
    {
        return [
            'id' => $this->id,
            'title' => $this->title,
            'emotions' => $this->emotions
        ];
    }
}
0 likes
2 replies
newbie360's avatar
Level 24

use accessor instead of this ? since you already have the collection and label is base on emotion_id

Corbin's avatar

Just expanding on @newbie360 answer. Here's the code:

Record.php

class Record extends Model
{
    use HasFactory;

    /*protected $casts = [
        'emotions' => 'json',
    ];*/

    public $emtionsLabels = ['Happy', 'Sad'];

    public function getEmotionsAttribute($value)
    {
        $emotions = json_decode($value, true);
        foreach($emotions as $emotion){
            $newEmotions[] = 
                [
                    'intensity' => $emotion['intensity'],
                    'new_intensity' => $emotion['newIntensity'],
                    'emotion_id'=> $emotion['emotion_id'],
                    'label' => $this->emtionsLabels[$emotion['emotion_id'] - 1]
                ];
        }

        return collect($newEmotions); 
    }
}

Please or to participate in this conversation.