PHP Laravel 如何查询字段类型为json的数据-WhereJsonContains、orWhereJsonContains

创建表

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();

来源地址：https://blog.csdn.net/wendyNo/article/details/131087626