1 year ago

Saving an array into a JSON column in a MySQL database

Posted 1 year 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')
              ->where('occupation_id', '=', $task_statement->occupation_id)

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

              ->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.