Is it possible to pass a postgres json selector as a parameter in php using pdo-CodePudding

Consider the following json describing files stored in a json column in a postgres database:

{
  "e8af43c1-16d3-4f9b-900b-0abec048aa62": { "name": "foo", "size": 1024 },
  "1d83d835-d7d2-4433-adb7-05dcd9b3397e": { "name": "bar", "size": 512 },
  ...
  "60394d82-7134-42ca-8fb0-1211360c3a72": { "name": "baz", "size": 1 }
}

In postgres, it is possible to select any one of these files from the json column by its key like:

SELECT json_column->'60394d82-7134-42ca-8fb0-1211360c3a72' FROM files

Is it possible to pass in a json selector as a parameter from php using pdo? I am imagining something like:

public function get_file_details($file_id) {
    $sql = "SELECT json_column->':file_id' FROM files";

    $stmt = $this->db->prepare($sql);
    $stmt->bindParam(':file_id', $file_id, PDO::PARAM_STR);
    $stmt->execute();

    return $stmt->fetchColumn();
}

But this does not work.

CodePudding user response：

This SQL will surely work in your case:

select
(
  select value from jsonb_each(files.json_column) as t(key, value)
  where key = :file_id
) from files;

So one line changes:

$sql = <<<SQLSTRING
select
(
  select value from jsonb_each(files.json_column) as t(key, value)
  where key = :file_id
) from files;
SQLSTRING;

The rest remains the same.