Saving an array into a JSON column in a MySQL database

Have a look at https://laravel.com/docs/5.7/eloquent-mutators#array-and-json-casting

You need to add your column to the casts property with array type

 protected $casts = [
    'tasks' => 'array',
  ];

Laravel will then handle the serialisation and de-serialisation of your data. You are currently using DB and Query Builder but would be trivial (and advisable) to use eloquent instead

First what you have in your example is (artisan command) is a string version of JSON.

This test was run in Postman as a post request with no special headers and a payload (body) of:

{
    "body": {
            "tasks" : [
                {
                        "task" : "Direct or coordinate an organization's financial or budget                activities to fund operations, maximize investments, or increase efficiency."
                },
                {
                        "task" : "Confer with board members, organization officials, or staff members to discuss issues, coordinate activities, or resolve problems."
                }
            ]
    }
}

So if you do this:

Route::post('/', function (Request $request) {

    $test = Test::create([
        'body' => json_encode( $request->body )
    ]);

    return response()->json($test->body);
});

Your response will look like:

"{\"tasks\":[{\"task\":\"Direct or coordinate an organization's financial or budget activities to fund operations, maximize investments, or increase efficiency.\"},{\"task\":\"Confer with board members, organization officials, or staff members to discuss issues, coordinate activities, or resolve problems.\"}]}"

Which is what you don't want.

On the other hand:

Route::post('/', function (Request $request) {

    $test = Test::create([
        'body' => json_encode( $request->body )
    ]);

    return $test->body;
});

Returns:

{"tasks":[{"task":"Direct or coordinate an organization's financial or budget activities to fund operations, maximize investments, or increase efficiency."},{"task":"Confer with board members, organization officials, or staff members to discuss issues, coordinate activities, or resolve problems."}]}

which is what you are looking for.

Issues to look for:

By default, laravel responds with JSON data if you return data not put into a view. So you (as above) need to ensure it is sent as JSON not a string version of JSON.

Notice I insert as json_encode(). If not the Illuminate\Http\Request sets JSON as an array:

array:1 [
  "tasks" => array:2 [
    0 => array:1 [
      "task" => "Direct or coordinate an organization's financial or budget activities to fund operations, maximize investments, or increase efficiency."
    ]
    1 => array:1 [
      "task" => "Confer with board members, organization officials, or staff members to discuss issues, coordinate activities, or resolve problems."
    ]
  ]
]

But if I json_encode() looks like:

"{"tasks":[{"task":"Direct or coordinate an organization's financial or budget activities to fund operations, maximize investments, or increase efficiency."},{"task":"Confer with board members, organization officials, or staff members to discuss issues, coordinate activities, or resolve problems."}]}"

Which is a stringified version.

Like above, don't be afraid to create a new Laravel project just to play around. Took me less than 5 minutes to play as above.

End of the day, you are fighting defaults from laravel. I assume you're newer at Laravel as you not using Eloquent. Don't be afraid to look at source code. Taylor left a lot of comments to explain a lot.

Also as a side note the string version can be consumed in javascript with:

JSON.parse($test->body)

@jekinney - In JavaScript would JSON.parse($test->body) not be JSON.parse(test.body);

Why are you using response($tasks)->original?

@D9705996

You have a valid point and good catch, I was trying to be quick, but you can inject PHP in javascript though not recommended (use props or data attribute etc.)

JSON.parse( {{ $test->body }} )

Thanks @D9705996 and @jekinney , I have taken your advice and used an Eloquent model, no idea why or how, but it works. @jekinney , your diagnosis of my Laravel skills is spot on, this is my first project.

@staudenmeir , I used response($tasks)->original because the array of tasks I was after was in an element of response($tasks) called original - as admitted above, I am new at this.

I now have:

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Hash;
use DB;
use App\Models\Occupation;

class ImportTasks extends Command {

  protected $signature = 'jobdesc:importtasks';
  protected $description = 'Imports tasks into a JSON column';

  public function handle() {

    $task_statements = DB::connection('master')->table('task_statements')->distinct()->select('occupation_id')->get();
    foreach ($task_statements as $task_statement) {

      $tasks = DB::connection('master')
              ->table('task_statements')
              ->where('occupation_id', '=', $task_statement->occupation_id)
              ->select('task')
//              ->orderBy('task')
              ->get();
      
      $occupation = Occupation::find($task_statement->occupation_id);
      $occupation->tasks = $tasks;
      $occupation->save();
      
    }

  }

}

which gives me the right stuff in my database:

[
    {
        "task" : "Direct or coordinate an organization's financial or budget activities to fund operations, maximize investments, or increase efficiency."
    },
    {
        "task" : "Confer with board members, organization officials, or staff members to discuss issues, coordinate activities, or resolve problems."
    },
    {
        "task" : "Analyze operations to evaluate performance of a company or its staff in meeting objectives or to determine areas of potential cost reduction, program improvement, or policy change."
    },
    {
        "task" : "Direct, plan, or implement policies, objectives, or activities of organizations or businesses to ensure continuing operations, to maximize returns on investments, or to increase productivity."
    },
    {
        "task" : "Prepare budgets for approval, including those for funding or implementation of programs."
    },
    {
        "task" : "Direct or coordinate activities of businesses or departments concerned with production, pricing, sales, or distribution of products."
    },
    {
        "task" : "Negotiate or approve contracts or agreements with suppliers, distributors, federal or state agencies, or other organizational entities."
    },
    {
        "task" : "Review reports submitted by staff members to recommend approval or to suggest changes."
    },
    {
        "task" : "Appoint department heads or managers and assign or delegate responsibilities to them."
    },
    {
        "task" : "Direct human resources activities, including the approval of human resource plans or activities, the selection of directors or other high-level staff, or establishment or organization of major departments."
    },
    {
        "task" : "Preside over or serve on boards of directors, management committees, or other governing boards."
    },
    {
        "task" : "Prepare or present reports concerning activities, expenses, budgets, government statutes or rulings, or other items affecting businesses or program services."
    },
    {
        "task" : "Establish departmental responsibilities and coordinate functions among departments and sites."
    },
    {
        "task" : "Implement corrective action plans to solve organizational or departmental problems."
    },
    {
        "task" : "Coordinate the development or implementation of budgetary control systems, recordkeeping systems, or other administrative control processes."
    },
    {
        "task" : "Direct non-merchandising departments, such as advertising, purchasing, credit, or accounting."
    },
    {
        "task" : "Deliver speeches, write articles, or present information at meetings or conventions to promote services, exchange ideas, or accomplish objectives."
    },
    {
        "task" : "Serve as liaisons between organizations, shareholders, and outside organizations."
    },
    {
        "task" : "Nominate citizens to boards or commissions."
    },
    {
        "task" : "Interpret and explain policies, rules, regulations, or laws to organizations, government or corporate officials, or individuals."
    },
    {
        "task" : "Make presentations to legislative or other government committees regarding policies, programs, or budgets."
    },
    {
        "task" : "Refer major policy matters to elected representatives for final decisions."
    },
    {
        "task" : "Administer programs for selection of sites, construction of buildings, or provision of equipment or supplies."
    },
    {
        "task" : "Direct or coordinate activities of businesses involved with buying or selling investment products or financial services."
    },
    {
        "task" : "Direct or conduct studies or research on issues affecting areas of responsibility."
    },
    {
        "task" : "Attend and participate in meetings of municipal councils or council committees."
    },
    {
        "task" : "Organize or approve promotional campaigns."
    },
    {
        "task" : "Conduct or direct investigations or hearings to resolve complaints or violations of laws or testify at such hearings."
    },
    {
        "task" : "Represent organizations or promote their objectives at official functions or delegate representatives to do so."
    },
    {
        "task" : "Prepare bylaws approved by elected officials and ensure that bylaws are enforced."
    },
    {
        "task" : "Review and analyze legislation, laws, or public policy and recommend changes to promote or support interests of the general population or special groups."
    }
]

Thank you all for your help, I can now go to bed and actually sleep instead of worrying about this.

@NettSite - Glad you managed to get your problem resolved. However I would strongly advise that you follow along with the beginner series on laracasts, in particular this one

https://laracasts.com/series/laravel-from-scratch-2018

It will help you get to grips with the fundamentals of the framework. For example in your example you are storing lots of tasks in a JSON column where as the right way to do this would be to have a separate task table with a hasMany/belongsTo relationships. Don't worry if this doesn't mean much right now but once you finish the series I linked it should all start to make sense.

@D9705996 , Hi.

The current database structure is as you suggest, with a SQL relationship between occupations and their tasks, and that is the normal way to normalise. However, I am using DataTables on the front, and a particular customer requirement will be more easily met by storing the tasks, and in fact alternate job titles, in some form of serialised columns in the occupations table. MySQL has gifted us with JSON, and as with all kids, I like new toys. Also, I had read about how Laravel makes it so easy to use JSON columns, so I was planning to take that for a drive.

I started by trying to set up my new structure in MySQL, importing the tasks from their table into a JSON column, but that proved a bit hairy, so I decided to hack a quick PHP script to do it, and then remembered about artisan, so thought I would use that.

Your suggestion pointed me to exactly the thing I was looking for, the easy JSON functionality. This is going to remove hundreds of lines of code from my application.

Thanks again for your help, it is actually awesome that total strangers can be so helpful. If only everything in life could be open source.