CODEWITHSUNDEEP
laraveleloquent
tux
tux

Reputation: 1287

Eloquent Select Query On JSON Columns

I have a JSON column for properties table. Below is the eloquent query to pick specific fields.

Company::select('id', 'information')
        ->with([
            'properties' => function($sql) {
                return $sql->select('id', 'company_id', 'information->>status');
            }
        ])
        ->get();

enter image description here

Is there a way to replace the response with the actual key?

Upvotes: 0

Views: 665

Answers (1)

Arun A S
Arun A S

Reputation: 7006

The simplest method to get the key would be to to give it an alias. So something like

$sql->select('id', 'company_id', 'information->status as status');
$sql->select('id', 'company_id', 'information->status as information_status');
$sql->select('id', 'company_id', 'information->status as information.status');

Edit

If you wish to maintain the structure of the response, then its better to create a Resource and let it handle the structure. So

<?php

namespace App\Http\Resources;

use Illuminate\Http\Resources\Json\JsonResource;
use Illuminate\Http\Resources\Json\ResourceCollection;

class Properties extends JsonResource
{
    /**
     * Transform the resource into an array.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return array
     */
    public function toArray($request)
    {
        $information = ['status'=>optional($this->information)['status']];
        return [
            'id' => $this->id,
            'company_id' => $this->company_id,
            'information' => $information,
        ];
    }
}

Similarly create a Resource for Company with the fields you want.

Upvotes: 1

Related Questions