We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Postgres

So, I'm trying to query postgres using:

$connection = $this->db;
$data = $connection->query($sql);
$results = $data->fetchAll();

This works wonders except when I try to use json fields. Usually, I find some kind of workaround but honestly I would like to solve this without having to apply tricks to my sql query.

I'd like to run this query:

SELECT *
FROM "event" AS e
WHERE value ? 'campaign_code'

It queries for all events returning those with the key campaign_code present in the value column (jsonb field)

When I try to run this, the "?" is interpretad as a binding and shouldn't (in this case), causing me an error.

SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "$1"
LINE 23:      WHERE value $1 'campaign_code'

Any thoughts?



6.7k

use jsonb_exists(value, 'campaign_code')



939

Is that documented somewhere? I must have missed it.



6.7k

I found it in stack overflow, but I can't find any documents in postgresql.org



939

Btw, have you found an equivalent for "@>"?



6.7k

maybe jsonb_contain? or jsonb_contained?