MYSQL - If I have a column of type json can I Select based on what's in the JSON

Question

I have a database that holds recordings and each recording has custom metadata that the user can specify, which will be different every time. Is it possible when I do my GET to Select based on what is in that metadata?

<?php
require 'config.php';
require 'database_connector.php';

if (!$_SERVER['REQUEST_METHOD'] === 'GET') {
    var_dump(http_response_code(403));
    die();
}

$secret = "getSecret";
if ($secret !== $_GET['secret']) {
    var_dump(http_response_code(401));
    die();
}

$request = (object) [
    'id' => $_GET['id'], 'game_name' => $_GET['game_name'], 'gamer_tag' => $_GET['gamer_tag'], 'limit' => $_GET['limit'], 'id' => $_GET['meta_data']
];
$connection = openConnection($config);
retrieveRecordings($connection, $config, $request);
$connection->close();

function retrieveRecordings($connection, $config, $request)
{
    $stmt = $connection->prepare("SELECT * FROM $config->tablename WHERE id = ? OR game_name = ? OR gamer_tag = ? OR meta_data = ? LIMIT ?");
    $stmt->bind_param("sssss", $request->id, $request->game_name, $request->gamer_tag, $request->meta_data, $request->limit);
    if (!$stmt->execute()) {
        echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error . " \r\n";
        die();
    }
    $result = $stmt->get_result();
    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $id = $row["id"];
            $game_name = $row["game_name"];
            $gamer_tag = $row["gamer_tag"];
            $recording = $row["recording"];
            $meta_data = $row["meta_data"];
            $hex = bin2hex($recording);
            echo ("response=id=" . $id . "\r\n");
            echo ("response=game_name=" . $game_name . "\r\n");
            echo ("response=gamer_tag=" . $gamer_tag . "\r\n");
            echo ("response=meta_data=" . $meta_data . "\r\n");
            echo ("response=recording" . $id . "=" . $hex . "\r\n");
        }
    } else {
        echo "0 results \r\n";
    }
}

It is probably possible. Please provide sample data, expected results, and your current attempt at solving the problem, so one can try and give you a relevant solution. — GMB, Nov 10 '19 at 23:48
See https://stackoverflow.com/questions/30411210/how-to-search-json-data-in-mysql — Ultimater, Nov 11 '19 at 00:29

score 1 · Accepted Answer · answered Nov 11 '19 at 00:12

In MySQL 5.7, there are JSON functions so you can do certain kinds of searches. See JSON function reference for documentation and examples.

There are some kinds of searches that are too complex to do with the MySQL 5.7 JSON functions. So in MySQL 8.0, they introduced JSON_TABLE() which is complex to use, but it kind of transforms a JSON document into virtual rows and columns, so you can do anything you can normally do with an SQL WHERE clause.

But in either case, if the user can store arbitrary metadata, like deeply nested JSON arrays or objects, it's going to be quite difficult to make it searchable. What fields are present in the JSON? How deep are they in the JSON? How likely is it that the user will know how to write JSON search syntax?

Frankly, I wouldn't allow a user to store arbitrary JSON, and I wouldn't allow a user to run an arbitrary search. That sounds like a nightmare to support, impossible to optimize, and probably a security vulnerability.

P.S. You have the key 'id' twice in this line. This won't work, you can only have each key once in a hash or an object. I guess you meant for the second 'id' to be 'meta_data'.

'id' => $_GET['id'], 'game_name' => $_GET['game_name'], 'gamer_tag' => $_GET['gamer_tag'],
  'limit' => $_GET['limit'], 'id' => $_GET['meta_data']

MYSQL - If I have a column of type json can I Select based on what's in the JSON

1 Answers1