Question 408017 in Mysql Question posted in
The official MySQL documentation can be found here.

How to set a JSON value with JSON_replace MySQL function?

my code:

$newElements=json_encode(['name'=>'jorge','age'=>23]);

mysqli_query($db, "UPDATE dl SET data=json_replace(data, '$.list', cast('$newElements' as json)) WHERE id = '$ID' ")

my Error:

Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘json)) WHERE id = ‘8451’…

That my list of:

['name' => 'jak', 'age' => 13] let it be: ['name' => 'jorj', 'age' => 21]
Tags:

2

Answers


  1. 0 
    
$newElements = json_encode(['name' => 'jorge', 'age' => 23]);

// Ensure the new elements are properly escaped for use in the SQL query
$newElements = mysqli_real_escape_string($db, $newElements);

$query = "
    UPDATE dl 
    SET data = JSON_REPLACE(data, '$.list', CAST('$newElements' AS JSON))
    WHERE id = '$ID'
";

mysqli_query($db, $query) or die(mysqli_error($db));
    Login or Signup to reply.
  2. 0

    Check our path (JSON path) and also try to pass the elements with full path like the code below:

    UPDATE `users` SET `chamber`  =  JSON_REPLACE(`chamber`, '$.name', 'Ahmed', '$.age', 31);

    Suppose we have users table and chamber col (JSON col), containing this ({"age": 33,"name": "Khaled"}) after the above query you will get the updated values ({"age": 31,"name": "Ahmed"}) and don’t forget the where clause ;).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search