Mastering JSON Fields with ThinkORM in PHP 8 – Insert, Query, and Update

Installation

composer require topthink/think-orm

Working with JSON Columns

Assume a user table with a JSON column info. The json() method tells ThinkORM which columns contain JSON so that it can encode/decode automatically.

Insert JSON data

$data = [
    'id'       => 2024,
    'username' => 'Tinywan',
    'info'     => [
        'email'  => '[email protected]',
        'gender' => '女',
        'age'    => 24,
    ],
];

$res = think\facade\Db::table('user')
    ->json(['info'])
    ->insert($data);

Query the whole JSON column

$user = think\facade\Db::table('user')
    ->json(['info'])
    ->find(1);

dump($user);

Query using JSON fields as conditions

$user = think\facade\Db::table('user')
    ->json(['info'])
    ->where('info->email', '[email protected]')
    ->find();

dump($user);

If a JSON attribute stores a numeric value, its type must be declared explicitly; otherwise the condition is treated as a string.

$user = think\facade\Db::name('user')
    ->json(['info'])
    ->where('info->user_id', 10)
    ->setFieldType(['info->user_id' => 'int'])
    ->find();

dump($user);

Update JSON data

Full JSON replacement

$data['info'] = [
    'email'    => '[email protected]',
    'nickname' => 'Tinywan',
];

think\facade\Db::table('user')
    ->json(['info'])
    ->where('id', 2024)
    ->update($data);

Single field update

$data['info->nickname'] = 'Tinywan';

think\facade\Db::name('user')
    ->json(['info'])
    ->where('id', 1)
    ->update($data);

Model‑Based JSON Handling

Define a model and list JSON columns in the $json property.

<?php
namespace app\model;
use think\Model;

class User extends Model {
    protected $json = ['info'];
}

Insert via model

$user = new User;
$user->name = 'Tinywan';
$user->info = [
    'email'    => '[email protected]',
    'nickname' => '阿克苏',
];
$user->save();

Object style is also supported:

$user = new User;
$user->name = 'Tinywan';
$info = new \StdClass();
$info->email    = '[email protected]';
$info->nickname = '阿克苏';
$user->info = $info;
$user->save();

Query via model

$user = User::find(1);

echo $user->name;          // Tinywan
echo $user->info->email;   // [email protected]
echo $user->info->nickname;// 阿克苏

$user = User::where('info->nickname', '阿克苏')->find();

Update via model

$user = User::find(1);
$user->name = 'Tinywan';
$user->info->email    = '[email protected]';
$user->info->nickname = 'Tinywan';
$user->save();

If the model is configured to return JSON as an array, assign the array back before saving:

$user = User::find(1);
$user->name = 'Tinywan';
$info = [
    'email'    => '[email protected]',
    'nickname' => 'Tinywan',
];
$user->info = $info;
$user->save();

Advanced JSON Queries

Object‑style JSON field

Column rest_day_rule stores {"start_time":"09:00"}.

SELECT * FROM attendance_group
WHERE rest_day_rule->'$.start_time' = '09:00';

$record = think\facade\Db::table('attendance_group')
    ->json(['rest_day_rule'])
    ->where('rest_day_rule->start_time', '09:00')
    ->find();

Array‑style JSON field

Column config holds an array of shift objects. To find rows containing a specific shift_id:

SELECT * FROM attendance_group_time
WHERE JSON_CONTAINS(config, JSON_OBJECT('shift_id', 1));

$shiftId = 1;
$record = AttendanceGroupTimeModel::json(['config'])
    ->whereRaw('JSON_CONTAINS(config, JSON_OBJECT("shift_id",' . $shiftId . '))')
    ->findOrEmpty();

class AttendanceGroupTimeModel extends BaseModel {
    protected $table = 'attendance_group_time';
    protected $json  = ['config'];
}