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

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