Try it yourself.

Introduction

In an application, there is often a need to store information which, despite being classifiable under a common typology, nonetheless contain different data, for example, a table of events that contains the information like logging, particular activities on data and so on; a minimal solution can be a table with three fields:

event type
JSON particular data
timestamp (if not in the above field)

However, with this solution, the problem arises of recovering such data perhaps in a form compatible with relational tools id is a table, hence this work, in fact, illustrates a possible solution to this problem using the PHP Data Objects (PDO) extension.

Background

The JSON functions and operators were introduced in the SQLite release 3.37.2 and are built by default from the version 3.38.0 (2022-02-22)1.

The PHP script described in this document does not use the features offered by SQLite and can therefore be used with versions of SQLite lower than those mentioned above.

This is an update of the product to overcome its limitations, in particular in the previous version the JSON objects had to have the same structure this, incidentally, hid, without consequences, a conceptual error.

The software has been tried with Data base SQLite and MariaDB (Mysql) .

Using the Code

The script json2table.php contains the class of the name J2t; the class contains three public functions:

json2table
execSQL
extractFields

The first two functions assume that the database has already been opened (i.e., by creation of a new PDO object).

The scope of the first one is to create a temporary table using an array of JSON Objects or an SQL statement, the second is also used internally.

Generation of the Temporary Table

The json2table function structure is:

json2table($dbh,$data,$tableName="TemporaryTable",$flds=array())

where:

$dbh is an instances of the PDO base class
$data is an array of JSON objects or an SQL SELECT statement
$tableName is the name of the temporary table created
$flds is an optional array of field names to extract from input for the table to be created.

The input data

If the second parameter of the json2table function is a SQL SELECT command, the fields involved can be JSON strings and possibly normal fields, in particular, the JSON field(s) can be an object, i.e., a set of name/value pairs or an array of dimension 1 containing an object.

Object	`{"Author":"Alan Perlis","Source":"","Quote":"Syntactic sugar causes cancer of the semicolon."}`
Object in array	`[{"_id":"9sgo4-hjyyq","author":"The Buddha","content":"Radiate boundless love towards the entire world — above, below, and across — unhindered, without ill will, without enmity.","tags":["Wisdom","Love"],"length":122,"dateAdded":"2023-03-30"}]`

An item can be an array, for example, the above "tags":["Wisdom","Love"] becomes a table field with name tags and value Wisdom, Love.

The table structure

The fields of the generated table will be those of the first element of the input data set or those indicated in the possible fourth parameter of the method, this parameter is an array of field names and possibly data type.

include 'json2table.php';
$DBname = "json.sqlite";
$db = new PDO("sqlite:$DBname");
$j2table = new J2t;
...
$sql = "SELECT * FROM Advanced";
$ans = $j2table→json2table($db,$sql,$tTable,array("Store","Qty","Box"=>"TEXT");

The field data type is inferred from the input data unless indicated in the fourth parameter; the value accepted are TEXT, INTEGER and REAL.

The usefulness of the fourth parameter is in being able:

to obtain a subset of the data present,
to possibly indicate the type of data, for example, treating data stored as character as numeric,
to indicate field(s) not present in the first row.

The output data

The function returns:

an empty string if the table was created,
No Data! If the JSON array is empty or the SELECT statement returns an empty set.

It is also created a temporary table for possibly errors.

Example 1: Use With Array of Data and Call the Class Function

...
include 'common/json2table.php';
$dbh = new PDO("sqlite:DataBaseFile");
...
$sql = "SELECT Data FROM Loggin WHERE Type = 'MoveDrugs';
$sth = $dbh->prepare($sql);
$sth->execute();
$result = $sth->fetchAll(PDO::FETCH_COLUMN, 0);    
$table = "tTable";
J2t::json2table($dbh,$result,$table);
...

Execute SQL Commands

The function is used by the json2table function but is also a method and can be used for execute SQL commands:

execSQL($dbh,$sql,$parms=[])

The optional $parms is an array of values with the elements that are bound in the SQL statement being executed, see the Example 2 below.

The function returns a PDOStatement object or an error if the SQL isn’t correct, in order to obtain the error, the function sets the error mode to SILENT; the previous mode is restored after the command execution.

ExecSQL Function

public static function execSQL($dbh,$sql,$parms=[]) {
    $errMode = $dbh→getAttribute(PDO::ATTR_ERRMODE); // save previous ATTR_ERRMODE
    $dbh->setattribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_SILENT);    // disable crash
    $sth = $dbh->prepare($sql);
    $a = $dbh->errorInfo();
    $dbh->setattribute(PDO::ATTR_ERRMODE,$errMode);  // restore previous ATTR_ERRMODE
    if (!$sth) {return "\n<br>$sql\n<br>".$a[2];}
    $sth->execute($parms);
    return $sth;
}

Example 2: Use with SQL and Call of Class Method

...
include 'common/json2table.php';
$dbh = new PDO("sqlite:DataBaseFile");
...
$sql = "SELECT Data FROM Loggin WHERE Type = 'MoveDrugs'";
$y2t = new J2t;
$answer = $y2t->json2table($dbh,$sql,"t");
if ($answer != "") exit($answer);		// Something went wrong
$result = $y2t->execSql($dbh,"SELECT Count(*) Count, SUM(Qty) Qty FROM t");
if (gettype($result) == "string") echo $result;  // SQL problem
else print_r($result→fetchAll(PDO::FETCH_ASSOC));
$sql = "SELECT * FROM t WHERE Qty > ?";
print_r($y2t->execSql($dbh,,Array(100))->fetchAll(PDO::FETCH_ASSOC));
...

History

31^st March, 2023
- Initial version.
11^th May, 2023
- Input data from SQL SELECT can contain normal fields and one or more JSON field(s),
- an item of JSON field can be an array.
18^th March, 2024
- Choice on fields,
- enhanced type management,
- enhanced error management.

Create Table SQLite and MariaDB (Mysql) from JSON Object using PHP PDO.