PDO绑定数组索引(postgresql JSONB)用于读取


PDO bind array index (postgresql JSONB) for reading

我在postgresql JSONB列中存储对象数组,我试图加载第n个对象的属性,但未能将其与PDO绑定。

测试设置

<?php
error_reporting(E_ALL);
ini_set('display_errors',1);
$Db = new PDO('...');

    $Db->exec('CREATE TABLE public.test (id SERIAL PRIMARY KEY, jb JSONB NOT NULL )');
    $insert = $Db->prepare('INSERT INTO public.test(jb) VALUES (?)');
    $insert->execute([json_encode([
                                     'q' => 'Qst1'
                                     , 'a' => [['txt' => 'Ans1', 'isCorrect' => true], ['txt' => 'Ans2', 'isCorrect' => false], ['txt' => 'Ans3', 'isCorrect' => false], ['txt' => 'Ans4', 'isCorrect' => false]]
                                 ], JSON_NUMERIC_CHECK)]);

如果我读取整个数组,我可以检查

$allAnswers = $Db->prepare("select jb->'a' from public.test where id=?");
$allAnswers->execute([1]);
$result  = json_decode($allAnswers->fetchColumn(),true);
foreach ([0,1,2,3,17] as $answerId) {
    if (!array_key_exists($answerId,$result) ) {
        echo $answerId,':','NULL',"'n";
    }
    else {
        $r = $result[$answerId]['isCorrect'];
        echo $answerId,':',($r?'TRUE':'FALSE'),"'n";
    }
}

但如果我只需要n个,我不知道如何绑定(我没有得到错误只有空值)

$answerIsCorrect = $Db->prepare("select jb->'a'->?->>'isCorrect' from public.test where id = ?");
foreach ([0,1,2,3,17] as $answerId) {
    $succ = $answerIsCorrect->execute([$answerId, 1]);
    if (true !== $succ) {
        die('Execute failed');
    }
    $result = $answerIsCorrect->fetchColumn();
    echo $answerId,':',is_null($result)?'NULL':('true'===$result?'TRUE':'FALSE'),"'n";
}

电流输出

0:NULL 1:NULL 2:NULL 3:NULL 17:NULL

除外输出

0:TRUE 1:FALSE 2:FALSE 3:FALSE 17:NULL

终于找到了

$answerIsCorrect = $Db->prepare("select jsonb_extract_path(jb->'a',?)->>'isCorrect' from public.test where id = ?");
不是

$answerIsCorrect = $Db->prepare("select jb->'a'->?->>'isCorrect' from public.test where id = ?");
工作