Re: JSON query help

On Wed, 28 Aug 2019 at 21:43, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Gavin Henry <gavin(dot)henry(at)gmail(dot)com> writes:
> > This is 9.6 with BDR in.
>
> Um ... I bet not. Maybe you're confusing the version of psql with
> that of the server? Because jsonb_build_object() is definitely
> there in 9.6.

Ah, yes. My apologies. 9.4.15 with BDR the server shows. I'm still
investigating what to do to get on to v11 (probably v12) and am
looking at rolling out Pgpool II with each client. What are others
doing here? Hot Standby? Currently reading
https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling
(any other advice?).

> > ERROR: operator does not exist: jsonb @> json
>
> This one you just need a cast for --- the server won't automatically
> convert json to jsonb. (I don't remember why we didn't make that
> cast implicit, but probably there was a good reason.)

Thanks, will do.

> > How can I check if that function does exist?
>
> On a 9.6 server, you should get
>
> =# \df jsonb_build_object
> List of functions
> Schema | Name | Result data type | Argument data types | Type
> ------------+--------------------+------------------+---------------------+--------
> pg_catalog | jsonb_build_object | jsonb | | normal
> pg_catalog | jsonb_build_object | jsonb | VARIADIC "any" | normal
> (2 rows)
>
> If, as I suspect, it's really 9.4, you could substitute
> json_build_object(...)::jsonb
>
> regards, tom lane

Yep, emtpy:

\df jsonb_build_object
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)

Will use your suggestion and report back.