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

The #> operator works in SELECT, e.g. does not fail on when JSON column
contains JSON values. Thanks for the tip!

However when using it in the WHERE clause I get no result.

select * from jtest;
data
-------------
1
1
{"a" : "b"}
(3 rows)

select data #> '{"a"}' from jtest;
?column?
----------

"b"
(3 rows)

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.

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 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 :)

Just a note though. It took us a while to track down the problem. We have a
table with several million rows, and suddenly our queries started failing,
since someone had started to insert scalars. Others might also struggle to
figure out what is wrong if they bump into this behavior.

Cheers,
Alf

On 10 March 2014 22:42, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Mon, Mar 10, 2014 at 8:09 AM, <alf(dot)kristian(at)gmail(dot)com> wrote:
>
>> The following bug has been logged on the website:
>>
>> Bug reference: 9519
>> Logged by: Alf Kristian Støyle
>> Email address: alf(dot)kristian(at)gmail(dot)com
>> PostgreSQL version: 9.3.2
>> Operating system: Red Hat 4.6.3-2
>> Description:
>>
>> Steps to reproduce:
>> create table jtest (data json);
>> => CREATE TABLE
>>
>> insert into jtest (data) values ('1');
>> => INSERT 0 1
>>
>>
>> select data->>'foo' from jtest;
>> => ERROR: cannot extract element from a scalar
>>
>>
>> I think the insert should fail, since '1' is not valid JSON.
>>
>> After the data is in the database every query using the ->> operator,
>> hitting the row containing '1' will fail.
>>
>
> Lets say the value was instead {"a":1}.
>
> Now every query using data->'a'->>'b' will fail when it hits that row.
>
> So forbidding values does not fix the problem, it just moves it down a
> level.
>
> A possible solution is to make ->> return NULL (like it does for accessing
> values of non-existent keys) rather than raise an error when used on a
> scalar. Whether this would be an improvement, I don't know.
>
> Note that the construct:
> data #> '{a,b}'
> does return null in this case, and does not raise an error. You could
> argue that that is an inconsistency. On the other hand, you could argue it
> provides you with the flexibility to accomplish different things depending
> on which you desire.
>
> So if you want the NULL behavior, you could use this to get it:
>
> data #>> '{foo}'
>
>
> Cheers,
>
> Jeff
>