目录
json字段判断(json字段中有[],需要判断就可以使用)
json字段查询
字段格式
查询:
json_extract(data, '$.email.value')
注意:
如果字段不存在返回null;如果json字段中是null,返回字符串“null”;
eg:
$serviceUserStr = "'admin'";
$res = $this->connetion->executeQuery("select json_extract(data, '$.email.value') email,json_extract(data, '$.avatar.value') avatar from users")->fetchAll();
$res = array_map(function ($value){
if ($value['email'] == 'null') $value['email'] = null;
if ($value['avatar'] == 'null') $value['avatar'] = null;
return $value;
}, $res );
var_dump($res);die;
json字段更新
update meeting set detail = JSON_REPLACE(detail, '$.service', '$res') where id = 1001
json字段作为条件判断
字段格式
代码:
select id from meeting where JSON_CONTAINS(detail->'$.service',JSON_OBJECT('user_id', 'ceshi1'))
json字段判断(json字段中有[],需要判断就可以使用)
select * from meeting where detail->'$.leader' = json_array() or detail->'$.leader' is null