NettSite
1058
8
Laravel

Saving an array into a JSON column in a MySQL database

Posted 10 months ago by NettSite

I am trying to save a bunch of related records into a JSON column in a MySQL database, instead of having the records in a related table, which is where they are at present.

If I do this manually, in MySQL Workbench, it works hunky dory, and I end up with something like this, which is what I want:

{
    "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."
        }
    ]
}

When I do the same thing from an artisan command, I get:

{
    "tasks" : "[{\"task\":\"Administer programs for selection of sites, construction of buildings, or provision of equipment or supplies.\"}]"
}

As you can see, all the quote characters are escaped which breaks the JSON.

This is my code:

  $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')
              ->limit(1)
              ->get();

//      dd(json_encode(response($tasks)->original));

      DB::connection('master')
              ->table('occupations')
              ->where('id', '=', $task_statement->occupation_id)
              ->update(['task_statements->tasks' => response($tasks)->original]);
    }
  }

I have tried every combination of stripping slashes, adding slashes, json encoding and so which I can think of and I have to admit I am a sad and beaten person.

Any suggestions will be helpful.

Please sign in or create an account to participate in this conversation.