저도 테스트 해봤는데 잘 되네요. 혹시나 해서 질문자님 데이터를 최대한 그대로 쓰는 상황을 만들어서 테스트 해봤습니다.
php artisan make:migration CreateContactsTable --create contacts
class CreateContactsTable extends Migration { public function up() { Schema::create('contacts', function (Blueprint $table) { $table->increments('id'); $table->json('db_content'); $table->timestamps(); }); } }
php artisan migrate
php artisan make:model Contact
class Contact extends Model { protected $fillable = ['db_content']; }
php artisan tinker >>> App\Contact::create(['db_content' => '{"\uc774\ub984":"\uae40\ud604\uc2b9","\uc5f0\ub77d\ucc98":"01089878824","\uc0c1\ub2f4\ub0b4\uc6a9":"\ub300\ud658\ub300\ucd9c"}']); => App\Contact {#663 db_content: "{"\uc774\ub984":"\uae40\ud604\uc2b9","\uc5f0\ub77d\ucc98":"01089878824","\uc0c1\ub2f4\ub0b4\uc6a9":"\ub300\ud658\ub300\ucd9c"}", updated_at: "2017-02-03 07:36:31", created_at: "2017-02-03 07:36:31", id: 1, } >>> App\Contact::create(['db_content' => '{"\uc774\ub984":"\ucd5c\uac11\ub9bc","\uc5f0\ub77d\ucc98":"01041719560","\uc0c1\ub2f4\ub0b4\uc6a9":"\ub300\ud658\ud558\uace0\uc2f6\uc2b5\ub2c8\ub2e4 \ub300\ucd9c\uc804\ud654\uac00\ub9ce\uc544\uc11c\ubb38\uc790\uc8fc\uc2dc\uace0 \uc804\ud654\uc8fc\uc138\uc694"}']); => App\Contact {#661 db_content: "{"\uc774\ub984":"\ucd5c\uac11\ub9bc","\uc5f0\ub77d\ucc98":"01041719560","\uc0c1\ub2f4\ub0b4\uc6a9":"\ub300\ud658\ud558\uace0\uc2f6\uc2b5\ub2c8\ub2e4 \ub300\ucd9c\uc804\ud654\uac00\ub9ce\uc544\uc11c\ubb38\uc790\uc8fc\uc2dc\uace0 \uc804\ud654\uc8fc\uc138\uc694"}", updated_at: "2017-02-03 07:37:13", created_at: "2017-02-03 07:37:13", id: 2, } >>> App\Contact::where('db_content->연락처', '01089878824')->first(); => App\Contact {#667 id: 1, db_content: "{"이름": "김현승", "연락처": "01089878824", "상담내용": "대환대출"}", created_at: "2017-02-03 07:36:31", updated_at: "2017-02-03 07:36:31", }
다음과 같이 테스트 했습니다.
$ php artisan make:model Test -m
<?php // create_tests_table.php public function up() { Schema::create('tests', function (Blueprint $table) { $table->increments('id'); $table->json('col'); $table->timestamps(); }); }
$ php artisan migrate
$ php artisan tinker >>> $t = new App\Test => App\Test {#644} >>> $t->col = json_encode(['foo' => 'bar', 'baz' => 'qux']) => "{"foo":"bar","baz":"qux"}" >>> $t->save(); => true >>> DB::listen(function ($q) { var_dump($q->sql); }) => null >>> App\Test::where('col->foo', 'bar')->first() string(56) "select * from `tests` where `col`->'$."foo"' = ? limit 1" => App\Test {#671 id: 1, col: "{"baz": "qux", "foo": "bar"}", created_at: "2017-02-02 14:18:48", updated_at: "2017-02-02 14:18:48", } >>> App\Test::where('col->baz', 'like', '%q%')->first() string(59) "select * from `tests` where `col`->'$."baz"' like ? limit 1" => App\Test {#672 id: 1, col: "{"baz": "qux", "foo": "bar"}", created_at: "2017-02-02 14:18:48", updated_at: "2017-02-02 14:18:48", }
php version : 7.x
laravel version : 5.3
문제 :
데이터 베이스에 다음과 같이 db_content라는 column에 json data가 있습니다.
{"\uc774\ub984":"\uae40\ud604\uc2b9","\uc5f0\ub77d\ucc98":"01089878824","\uc0c1\ub2f4\ub0b4\uc6a9":"\ub300\ud658\ub300\ucd9c"}
{"\uc774\ub984":"\ucd5c\uac11\ub9bc","\uc5f0\ub77d\ucc98":"01041719560","\uc0c1\ub2f4\ub0b4\uc6a9":"\ub300\ud658\ud558\uace0\uc2f6\uc2b5\ub2c8\ub2e4 \ub300\ucd9c\uc804\ud654\uac00\ub9ce\uc544\uc11c\ubb38\uc790\uc8fc\uc2dc\uace0 \uc804\ud654\uc8fc\uc138\uc694"}
json의 key값은 처음부터 "이름", "연락처", "상담내용"입니다.
이 key값들을 이용해서 해당하는 value값을 갖고 있는 row를 query할려고 합니다.
문제는 여기서부터 입니다.
"이름"이라는 key값은 where로 query가 매우 잘 됩니다.
그런데 "연락처"라는 key값은 이상하게 query가 되질 않습니다...
DbManageField::whereRaw('db_content->"$.연락처" = "01089878824"')->get();
\DB::select('select * from db_manage_fields where db_content->"$.연락처" = ?', ['01089878824']);
제가 할 수 있는 모든 방법을 시도하여 query를 해보았으나...모두 무심하게도 빈 배열만 반환하는군요...
"이름"은 잘 되는데 "연락처"는 도대체 왜 query가 안 되는 걸까요?ㅜㅜ
도와주세요...