创建表
CREATE TABLE `suppliers` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`supplier_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '供应商名称',
`address` json DEFAULT NULL COMMENT '地址:数据格式{"city": "九江市", "country": "武宁县", "province": "江西省"}',
`s_type` json DEFAULT NULL COMMENT '1-贸易,2-生产,3-实验室,4-试剂,5-其他',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=37201 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
插入数据
INSERT INTO `suppliers` VALUES (1, "供应商1", '{"city": "九江市", "country": "武宁县", "province": "江西省"}',["1","5"]);
INSERT INTO `suppliers` VALUES (2, "供应商2", '{"city": "南京市", "country": "浦口区", "province": "江苏省"}',["1","5"]);
INSERT INTO `suppliers` VALUES (3, "供应商3", '{"city": "九江市", "country": "武宁县", "province": "江西省"}',["1","5"]);
单选查询地址、类型
DB::table('suppliers')->whereJsonContains('address.city', request()->get('address'))->first();
DB::table('suppliers')->whereJsonContains('s_type', request()->get('type'))->first();
多选查询地址、类型
$res= DB::table('suppliers')->where(function ($query) use ($request){
foreach($user_type as $val){
$query->orWhereJsonContains('type',[$val]);
}
})->first();
$res= DB::table('suppliers')->where(function ($query) use ($request){
foreach($user_type as $val){
$query->orWhereJsonContains('address',['city'=>$val]);
}
})->first();