Postgres JSONB support on zend/Db

[nikoz84]

Hi, how can i work with JSONB (postgresql) in zend framework 3?.

for example i want make query's like this

SELECT id, data->'title' AS title, data->'description' as description FROM tablename

SELECT jsonb_array_elements_text(data->'tags') as tag FROM tablename WHERE id = 1;

Or EXECUTE a function like a

SELECT myFunction() jsonb_each(jsonb)

INSERT INTO tablename VALUES (1,'{"title": "title 1","category":"category 2"}')

UPDATE tablename SET data = jsonb_set(data,'{"image","quality"}','{" copyright":"company X","registered":true}') where id=2;

ETC.

How can i implement this ideas, i follow the tutotial from ZF3 and i don't know how implement, maybe here is not the place for this question, but i appreciate if you give me a feedback, i don´t want to use ORM for this project. Thanks

[nikoz84]

This is the model file we can make a query with this methods

class EntityTable
{
    protected $tableGateway;
    protected $dbAdapter;
    public function __construct(TableGatewayInterface $tableGateway)
    {
        $this->tableGateway = $tableGateway;
        $this->dbAdapter = $tableGateway->adapter;
    }

public function getAllFromQuery()
{
     $query = $this->dbAdapter->query("SELECT count(*) FROM tablename WHERE data ? 'isVisible';", Adapter::QUERY_MODE_EXECUTE);
        $data = $query->toArray();

        return $data;
    }

it´s important than you have declare in your db adapter

'db' => [
        'driver' => 'Pgsql',  // pdo not support all the pg_ functions
        'database' => 'database',
        'username' => 'user',
        'password' => 'pass'
    ]

In a Action call

public function indexAction()
    {

        return new ViewModel([
            'data' =>$this->table->getAllFromQuery()
        ]);
    }

and the view

<?= var_dump($this->data) ?>

AND THE RESULT IS THAT

array(1) { [0]=> array(1) { ["count"]=> string(1) "6" } }

Well this is a want to take from the data base but, how can i pass the data array into a entity exchangeArray method??

[nikoz84]

Its not elegant but works!!

public function getAllFromQuery()
    {
        $query = $this->dbAdapter->createStatement("SELECT id,
                                                data->>'title' as title,
                                                data->>'acess' as access,
                                                data->>'category' as category
                                                FROM tablename");
        $data= $query->execute();
        /* OR if you want execute a function from postgres */

        //$query = $this->dbAdapter->query("SELECT totalregistrosdinamico('tablename')", Adapter::QUERY_MODE_EXECUTE);

        $resultSetPrototype = new ResultSet();
        $resultSetPrototype->initialize($data);
        $resultSetPrototype->setArrayObjectPrototype(new Model\EntityName());
        return $resultSetPrototype;

    }

[samsonasik]

there is Zend\Db\Sql\Predicate\Expression for that, I blogged about it https://samsonasik.wordpress.com/2017/03/13/querying-postgresqls-jsonb-with-zenddb/

[michalbundyra]

This repository has been closed and moved to laminas/laminas-db; a new issue has been opened at https://github.com/laminas/laminas-db/issues/80.