CODEWITHSUNDEEP
phpmysqlmongodblaravel
zwl1619
zwl1619

Reputation: 4232

How to remove `_id` field from the query result of mongoDB?

I am using laravel-mongodb(https://github.com/jenssegers/laravel-mongodb) to manipulate mongodb data.

I want to transfer data into mysql that queried from mongodb,there is a demo function to do this:

TestController.php

public function queryAndInsertMysql()
{
    $articles = DB::connection('mongodb')->collection('articles')->get()->toArray();

    DB::connection('mysql')->table('articles')->insert($articles);

    dd('ok');
}

There is an error when running this function:

SQLSTATE[42S22]: Column not found: 1054 Unknown column '_id' in 'field list' (SQL: insert into `articles` (`_id`, `content`, `title`) values (59ad186437dd1f1968004334, hello..., hello), (59ad186437dd1f1968004335, foo..., foo), (59ad186437dd1f1968004336, bar..., bar))

The error occurs because there is an _id field in mongodb collection,
and there is no _id field in mysql table,
so I need to remove _id field before insert them into mysql.

I try to do this with array_shift() but it doesn't work:

public function removeId()
{
    $articles = DB::connection('mongodb')->collection('articles')->get()->toArray();

    foreach ($articles as $article) {
        array_shift($article);
    }


    dd($articles);
}

The _id fields still exist:

enter image description here

What should I do?

Upvotes: 0

Views: 2806

Answers (3)

Alex Blex
Alex Blex

Reputation: 37128

You need to use projection to filter the _id out:

public function queryAndInsertMysql()
{
    $articles = DB::connection('mongodb')
        ->collection('articles')
        ->project(['_id' => 0])
        ->get()
        ->toArray();

    DB::connection('mysql')->table('articles')->insert($articles);

    dd('ok');
}

Please note that mongodb is shemaless, unless you enforce it with document validation, so it would be advisable to whitelist all the fields you expect to insert into SQL in the project argument:

public function queryAndInsertMysql()
{
    $articles = DB::connection('mongodb')
        ->collection('articles')
        ->project(['_id' => 0, 'content' => 1, 'title' => 1])
        ->get()
        ->toArray();

    DB::connection('mysql')->table('articles')->insert($articles);

    dd('ok');
}

It will guarantee that even if you accidentally have a document with any other field, it will be ignored.

Upvotes: 7

villaCebras
villaCebras

Reputation: 71

Laravel Collections map function can help you with this:

https://laravel.com/docs/5.4/collections#method-map

remove the _id field and the you can insert them into your MySQL DB

Upvotes: 1

Script47
Script47

Reputation: 14550

That is a nested array so you will need to use the extended foreach loop,

foreach ($arr as $key => $value) {
    /** Shift here... **/
}

Example

<?php
$a = [
    0 => [
        '_id' => 'sdgsdg'    
    ]
];

$y = '';

foreach ($a as $key => $val) {
    $y =  array_shift($a[$key]);  
}

var_dump($y); /** ---> string(6) "sdgsdg" **/
?>

Upvotes: 1

Related Questions