Skip to content

04-json-modification.md

Latest commit

 

History

History
217 lines (167 loc) · 4.31 KB

04-json-modification.md

File metadata and controls

217 lines (167 loc) · 4.31 KB

JSON Modification

Update JSON values and structure using PDOdb.

Setting JSON Values

jsonSet()

Update a JSON field by setting a value at a specific path:

// Update city in meta JSON
$db->find()
    ->table('users')
    ->where('id', 1)
    ->update([
        'meta' => $db->find()->jsonSet('meta', ['city'], 'London')
    ]);

Nested Paths

// Update nested value
// meta: { profile: { address: { city: "NYC" } } }
$db->find()
    ->table('users')
    ->where('id', 1)
    ->update([
        'meta' => Db::jsonSet('meta', ['profile', 'address', 'city'], 'Boston')
    ]);

Removing JSON Paths

Db::jsonRemove()

Remove a path from JSON:

// Remove a field from meta
$db->find()
    ->table('users')
    ->where('id', 1)
    ->update([
        'meta' => Db::jsonRemove('meta', ['old_field'])
    ]);

Remove Array Element

// Remove from JSON array
$db->find()
    ->table('users')
    ->where('id', 1)
    ->update([
        'tags' => Db::jsonRemove('tags', [1])  // Remove index 1
    ]);

Note: In SQLite, removing an array element sets it to null to preserve array indices.

Adding to JSON Arrays

Append to Array

use tommyknocker\pdodb\helpers\Db;

// Add tag to existing tags array
$db->find()
    ->table('users')
    ->where('id', 1)
    ->update([
        'tags' => Db::jsonArray(Db::raw('JSON_EXTRACT(tags, "$[*]")'), 'new_tag')
    ]);

JSON Object Updates

Merge Objects

// Add new field to existing object
$db->find()
    ->table('users')
    ->where('id', 1)
    ->update([
        'meta' => Db::raw('JSON_OBJECT(
            JSON_EXTRACT(meta, "$.existing_field"),
            "new_value",
            "additional_field", "additional_value"
        )')
    ]);

Batch JSON Updates

Update Multiple JSON Fields

$db->find()
    ->table('users')
    ->where('active', 1)
    ->update([
        'meta->city' => 'Updated City',
        'meta->updated_at' => Db::now()
    ]);

Replacing JSON Values

Db::jsonReplace()

Replace a JSON value at a path (only if path exists, does not create missing paths):

// Replace existing value
$db->find()
    ->table('users')
    ->where('id', 1)
    ->update([
        'meta' => Db::jsonReplace('meta', '$.status', 'inactive')
    ]);

// Try to replace non-existent path (won't create it)
$db->find()
    ->table('users')
    ->where('id', 1)
    ->update([
        'meta' => Db::jsonReplace('meta', '$.nonexistent', 'value')
    ]);
// Path won't be created if it doesn't exist

jsonSet vs jsonReplace

  • Db::jsonSet(): Creates path if missing, always sets the value
  • Db::jsonReplace(): Only replaces if path exists, does not create missing paths
// jsonSet creates path if missing
$db->find()
    ->table('users')
    ->where('id', 1)
    ->update([
        'meta' => Db::jsonSet('meta', '$.new_field', 'value')  // Creates path
    ]);

// jsonReplace only replaces if path exists
$db->find()
    ->table('users')
    ->where('id', 1)
    ->update([
        'meta' => Db::jsonReplace('meta', '$.another_field', 'value')  // Won't create path
    ]);

Common Patterns

Update User Preferences

$db->find()
    ->table('users')
    ->where('id', $userId)
    ->update([
        'preferences' => Db::jsonSet('preferences', ['theme'], 'dark'),
        'updated_at' => Db::now()
    ]);

Increment JSON Counter

// Increment counter in JSON metadata
$db->find()
    ->table('users')
    ->where('id', 1)
    ->update([
        'meta' => Db::raw('JSON_SET(meta, "$.login_count", 
                           COALESCE(JSON_EXTRACT(meta, "$.login_count"), 0) + 1)')
    ]);

Dialect-Specific Behavior

MySQL JSON_SET

UPDATE users SET meta = JSON_SET(meta, '$.city', 'London');

PostgreSQL jsonb_set

UPDATE users SET meta = jsonb_set(meta, '{city}', '"London"');

SQLite json_set

UPDATE users SET meta = json_set(meta, '$.city', 'London');

PDOdb handles these differences automatically.

Examples

Next Steps