Digging Down into JSON data with the MySQL Functions -- A Question from Peter Zaitsev -- Follow Up

tháng 3 23, 2016

Last time this blog covered digging into a JSON document in a MySQL 5.7 table. The goal was to pull certain records matching a particular criteria. Both Peter Zaitsev and Morgan Tocker get my thanks for their kind comments. My example was a little contrived in that an application would be used to fine tune seeking for a particular key value pair. I was trying to pull single records which is kind of silly when it is much easier to use PHP to parse the data. What follows below is a sample PHP script to grab out the matching records and then feed the results, the JSON document, into an array.


#!/usr/bin/php
<?php
$mysqli = new mysqli("localhost", "root", "hidave", "test");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$foo = array();
$query0 =
"SELECT* FROM restaurant WHERE json_contains(data, '{\"grade\": \"A\"}', '$.grades')";
echo "$query0\n";
if ($result = $mysqli->query($query0)) {
    $row = $result->fetch_row();
    printf("JSON is %s!\n\n", $row[0]);
    $foo =  json_decode($row[0]);
        var_dump($foo);
} else {
        printf("Errormessage: %s\n", $mysqli->error);

}


$mysqli->close();