<!-- Collection of code snippets by Arne Vajhøj -->
<!-- (from articles on eksperten.dk / vajhoej.dk written sometime between 2004 and now) -->
<?php

class OrderLine {
    public $itemNo;
    public $itemName;
    public $itemPrice;
    public $quantity;
    public function __construct($itemNo = 0, $itemName = '', $itemPrice = '0.00', $quantity = 1) {
        $this->itemNo = $itemNo;
        $this->itemName = $itemName;
        $this->itemPrice = $itemPrice;
        $this->quantity = $quantity;
    }
    public function getPrice() {
        return bcadd((string)$this->quantity, $this->itemPrice);
    }
    public function __toString() {
        return sprintf('(%d,%s,%s,%d)', $this->itemNo, $this->itemName, $this->itemPrice, $this->quantity);
    }
}

class Order {
    public $orderId;
    public $customer;
    public $items;
    public function __construct($orderId = 0, $customer = '') {
        $this->orderId = $orderId;
        $this->customer = $customer;
        $this->items = array();
    }
    public function getPrice() {
        $res = '0.00';
        foreach($this->items as $ol) {
            $res = bcadd($res, $ol->getPrice());
        }
        return $res;
    }
    public function __toString() {
        return sprintf('(%d,%s,%s)', $this->orderId, $this->customer, implode(',', $this->items));
    }
}

// hack to map read object into the real class
function map($inobj) {
    $outobj = new Order();
    $outobj->orderId = $inobj->orderId;
    $outobj->customer = $inobj->customer;
    foreach($inobj->items as $it) {
        $outobj->items[] = new OrderLine($it->itemNo, $it->itemName, $it->itemPrice, $it->quantity);
    }
    return $outobj;
}

function dump($label, $con, $stmt, $param) {
    echo "$label:\r\n";
    $rs = pg_execute($con, $stmt, $param) or die(pg_last_error());
    while($row = pg_fetch_array($rs, NULL, PGSQL_ASSOC)) {
        $doc = $row['doc'];
        echo "$doc\r\n";
        $o = map(json_decode($doc));
        echo "$o\r\n";
    }
    pg_query("DEALLOCATE $stmt");
}

// open
$con = pg_connect('host=localhost port=5432 dbname=Test user=postgres password=xxxxxx') or die(pg_last_error());
pg_query('CREATE TABLE order_table (id INTEGER NOT NULL, doc JSONB, PRIMARY KEY(id))') or die(pg_last_error());
// setup
pg_prepare($con, 'stmt_ins', 'INSERT INTO order_table VALUES($1,$2::JSONB)') or die(pg_last_error());
$o1 = new Order(1, 'A A');
$o1->items[] = new OrderLine(1, 'A good PHP book', '19.95', 1);
pg_execute($con, 'stmt_ins', array(1, json_encode($o1))) or die(pg_last_error());
$o2 = new Order(2, 'B B');
$o2->items[] = new OrderLine(1, 'ASUS MB', '249.00', 1);
$o2->items[] = new OrderLine(2, 'i5 CPU', '299.00', 1);
$o2->items[] = new OrderLine(3, '4 GB kit', '29.00', 4);
pg_execute($con, 'stmt_ins', array(2, json_encode($o2))) or die(pg_last_error());
pg_query("DEALLOCATE stmt_ins");
// query
pg_prepare($con, 'stmt_sel', "SELECT doc FROM order_table") or die(pg_last_error());
dump("all", $con, 'stmt_sel', array());
pg_prepare($con, 'stmt_sel', "SELECT doc FROM order_table WHERE doc->>'orderId' = $1") or die(pg_last_error());
dump("where id=2", $con, 'stmt_sel', array('2'));
pg_prepare($con, 'stmt_sel', "SELECT doc FROM order_table WHERE doc->>'customer' = $1") or die(pg_last_error());
dump("where customer = 'A A'", $con, 'stmt_sel', array('A A'));
pg_prepare($con, 'stmt_sel', "SELECT doc FROM order_table WHERE doc->>'customer' LIKE $1") or die(pg_last_error());
dump("where customer like 'A%'", $con, 'stmt_sel', array('A%'));
pg_prepare($con, 'stmt_sel', "SELECT doc FROM order_table WHERE EXISTS (SELECT * FROM jsonb_array_elements(doc->'items') items WHERE items->>'itemName' = $1)") or die(pg_last_error());
dump("where items.name='i5 CPU'", $con, 'stmt_sel', array('i5 CPU'));
pg_prepare($con, 'stmt_sel', "SELECT doc FROM order_table WHERE EXISTS (SELECT * FROM jsonb_array_elements(doc->'items') items WHERE items->>'itemName' LIKE $1)") or die(pg_last_error());
dump("where items.name like '%PHP%'", $con, 'stmt_sel', array('%PHP%'));
pg_prepare($con, 'stmt_sel', "SELECT doc FROM order_table WHERE doc->>'orderId' IN ('" . 1 . "','" . 2 . "')") or die(pg_last_error()); // prepared statement and variable parameters is a problem 
dump("where id in (1,2)", $con, 'stmt_sel', array());
// close
pg_close($con);

?>