Re: BUG #9519: Allows storing scalar json, but fails when querying

Thank you for your help, and thorough explanation.

Creating a new operator, is way out of my league, and we now have a good
way to work around our problems. So we are very pleased :)

I suppose if no one else complains, this shouldn't be fixed/changed, and
you shouldn't spend time creating a new operator.

Thanks again. Cheers,
Alf

On 11 March 2014 17:03, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Tue, Mar 11, 2014 at 1:32 AM, Alf Kristian Støyle <
> alf(dot)kristian(at)gmail(dot)com> wrote:
>
>>
>>
>> select data #> '{"a"}' from jtest where (data #> '{"a"}') = 'b';
>> ERROR: operator does not exist: json = unknown
>> LINE 1: ...CT data #> '{"a"}' from jtest where (data #> '{"a"}') = '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 = '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 run
>> (checking for existence):
>>
>> select data->>'a' from jtest where data->>'a' = '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 are
>> 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
>