Working with Json Functions in MySql from PHP

In MySQL, JSON functions are used to manipulate and extract data from JSON documents stored in JSON columns. These functions allow you to perform various operations on JSON data, such as inserting, updating, querying, and transforming JSON documents.

JSON_OBJECT.

This function creates a JSON object from a set of key-value pairs. It takes column names and values as arguments and returns a JSON object.

SELECT JSON_OBJECT('name', name, 'age', age) AS person FROM users;

 Using PHP

$name = 'John';
$age = 30;
$query = "SELECT JSON_OBJECT('name', :name, 'age', :age) AS person FROM users";
$stmt = $pdo->prepare($query);
$stmt->bindParam(':name', $name);
$stmt->bindParam(':age', $age);
$stmt->execute();
$result = $stmt->fetch(PDO::FETCH_ASSOC);
$person = $result['person'];
echo $person;

$query = "SELECT t1.id, JSON_OBJECT('name', t2.name, 'age', t2.age) AS person 
          FROM table1 AS t1
          JOIN table2 AS t2 ON t1.id = t2.table1_id";
$stmt = $pdo->prepare($query);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($result as $row) {
    $id = $row['id'];
    $person = $row['person'];
    // Perform necessary operations with the retrieved data
    // ...
}

JSON_ARRAY.

This function creates a JSON array from a set of values. It takes multiple arguments and returns a JSON array.

SELECT JSON_ARRAY('apple', 'banana', 'orange') AS fruits;

JSON_EXTRACT.

This function extracts a value from a JSON document based on a JSON path expression. It takes a JSON document and a JSON path as arguments and returns the extracted value.

SELECT JSON_EXTRACT(data, '$.name') AS name FROM json_table;

 Using PHP

$query = "SELECT JSON_EXTRACT(data, '$.name') AS name FROM json_table";
$stmt = $pdo->prepare($query);
$stmt->execute();
$result = $stmt->fetch(PDO::FETCH_ASSOC);
$name = $result['name'];
echo $name;

$query = "SELECT t1.id, t1.data, JSON_EXTRACT(t2.details, '$.name') AS name 
          FROM table1 AS t1
          JOIN table2 AS t2 ON t1.id = t2.table1_id";
$stmt = $pdo->prepare($query);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($result as $row) {
    $id = $row['id'];
    $data = $row['data'];
    $name = $row['name'];
    // Perform necessary operations with the retrieved data
    // ...
}

JSON_SET.

This function sets or updates a value in a JSON document. It takes a JSON document, a JSON path, and a new value as arguments and returns the modified JSON document.

UPDATE json_table SET data = JSON_SET(data, '$.age', 30) WHERE id = 1;

Using PHP

$id = 1;
$newAge = 30;
$query = "UPDATE json_table SET data = JSON_SET(data, '$.age', :newAge) WHERE id = :id";
$stmt = $pdo->prepare($query);
$stmt->bindParam(':newAge', $newAge);
$stmt->bindParam(':id', $id);
$stmt->execute();

$id = 1;
$newAge = 30;
$query = "UPDATE table1 AS t1
          JOIN table2 AS t2 ON t1.id = t2.table1_id
          SET t1.data = JSON_SET(t1.data, '$.age', :newAge)
          WHERE t1.id = :id";
$stmt = $pdo->prepare($query);
$stmt->bindParam(':newAge', $newAge);
$stmt->bindParam(':id', $id);
$stmt->execute();

JSON_REMOVE.

This function removes one or more values from a JSON document. It takes a JSON document and one or more JSON paths as arguments and returns the modified JSON document.

UPDATE json_table SET data = JSON_REMOVE(data, '$.address') WHERE id = 1;

 Using PHP

$id = 1;
$query = "UPDATE json_table SET data = JSON_REMOVE(data, '$.address') WHERE id = :id";
$stmt = $pdo->prepare($query);
$stmt->bindParam(':id', $id);
$stmt->execute();

$id = 1;
$query = "UPDATE table1 AS t1
          JOIN table2 AS t2 ON t1.id = t2.table1_id
          SET t1.data = JSON_REMOVE(t1.data, '$.address')
          WHERE t1.id = :id";
$stmt = $pdo->prepare($query);
$stmt->bindParam(':id', $id);
$stmt->execute();

JSON_CONTAINS.

This function checks if a JSON document contains a specific value. It takes a JSON document and a value as arguments and returns a Boolean value indicating whether the value is present in the JSON document.

SELECT * FROM json_table WHERE JSON_CONTAINS(data, '"apple"');

 Using PHP

$value = 'apple';
$query = "SELECT * FROM json_table WHERE JSON_CONTAINS(data, :value)";
$stmt = $pdo->prepare($query);
$stmt->bindParam(':value', $value);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
print_r($result);

Example creating a table with a JSON Column from PHP

$query = "CREATE TABLE your_table (
              id INT AUTO_INCREMENT PRIMARY KEY,
              data JSON
          )";
$stmt = $pdo->prepare($query);
$stmt->execute();

and Insert JSON data

$jsonData = json_encode(['name' => 'John', 'age' => 30]);

$query = "INSERT INTO your_table (data) VALUES (:jsonData)";
$stmt = $pdo->prepare($query);
$stmt->bindParam(':jsonData', $jsonData);
$stmt->execute();

If the size of the data is not too big, maybe no more than 100kB, instead of serializing and storing it as a blob, its better to use json format, this way you can query this data in a more efficient way using all the above functions. If you map this data to a DTO (read more on this topic: Using DTOs in PHP Symfony), then is pretty easy to handle very large and complex sets of data and moving them between client - server - database.