On Tue, Mar 11, 2014 at 1:32 AM, Alf Kristian St=F8yle <alf.kristian@gmail.=
com
> wrote:
>
>
> select data #> '{"a"}' from jtest where (data #> '{"a"}') =3D 'b';
> ERROR: operator does not exist: json =3D unknown
> LINE 1: ...CT data #> '{"a"}' from jtest where (data #> '{"a"}') =3D 'b';
> ^
> HINT: No operator matches the given name and argument type(s). You might
> need to add explicit type casts.
>
"#>>" returns text directly, just like ->> vs ->.
>
> select data #> '{"a"}' from jtest where (data #> '{"a"}')::text =3D 'b';
> ?column?
> ----------
> (0 rows)
>
>
> Am I doing a wrong conversion here, or is something else going on?
>
If you put the (data #> '{"a"}')::text construct in the select, you can
see what is going on. pulling out the element as JSON quotes the value (if
it not a number), because that is what JSON values are supposed to be, and
then converting to text leaves the quotes in place. So you are comparing
the 3 character '"b"' to the one character string 'b', and they are not
equal. If you use #>>, it is pulled out as text in the first place and the
quotes are not put on there.
> If the data in the database did not contain scalar values, then ->> works
> fine in WHERE. The following is almost the query we are actually trying r=
un
> (checking for existence):
>
> select data->>'a' from jtest where data->>'a' =3D 'b';
> ?column?
> ----------
> b
> (1 row)
>
>
> Regarding the ->> operator, I think it is unfortunate behavior it fails
> like that, I suppose we were expecting NULL behavior. However we are
> working around this, so if you don't think this should change, then we ar=
e
> fine with that :)
>
I don't really have an opinion on that, it just isn't obvious which way is
better--I can see times I would want either one. There are people who have
thought about this much more deeply than I have, but they haven't shown up
on this thread yet. (I think they are too busy over on the hackers list,
arguing over what behavior the next generation of json operators should
have.)
You can create a new operator with the behavior you want. I would like
some simple notation one could add to an operator or function invocation
which means "catch errors and convert to null", as I have several plperl
functions which I have created in two forms, one with an eval block and one
without. It would be nice to have one function with a run-time notation to
distinguish the behavior.
Cheers,
Jeff
