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

alf.kristian wrote
> If the JSON datatype accepts JSON values (not just objects), which I
> supposed is a valid approach, then shouldn't the JSON query operators be
> able to deal with that?
>
> This is the "unfortunate" part:
>
>
> *select data->>'foo' from jtest; => ERROR: cannot extract element from a
> scalar*
> So either, only accept JSON object (not simple JSON values), or make the
> JSON query operators work with JSON values as well.
>
> Having to add an extra check constraint here is not obvious, imho.
>
> For the record, we are working around this problem, through a small hack.
> Some other system is storing this kind of data in our database. We have
> reported a bug with them to fix it.
>
> This is not a big problem for us, but we love Postgres, so we thought we
> should report this.
>
> Cheers,
> Alf
>
>
>
> On 10 March 2014 20:52, Marko Tiikkaja <

> marko@

> > wrote:
>
>> On 3/10/14, 4:09 PM,

> alf.kristian@

> wrote:
>>
>>> I think the insert should fail, since '1' is not valid JSON.
>>>
>>
>> It's a valid "JSON value", it's just not an object. If you only want to
>> store JSON objects in the table, consider using a CHECK constraint.
>>
>>
>> Regards,
>> Marko Tiikkaja
>>

Not really sure how robust you expect the system to be in the face of
polymorphic columns.

SELECT ('["a","b","c"]'::json)->>'not_a_key' -- ERROR: cannot extract field
from a non-object

The system supposes that, at a structural level, you are dealing with
column-consistent data and so if you ask for something that does not make
sense (i.e., an object key when you have an array or a scalar) it will warn
you.

I guess, in theory, any de-referencing that does not find a valid target
could return NULL...though I'm not sure that is an improvement.

This is a relational database and so it is expected that a column defines a
single thing and that thing naturally can be one-of a scalar, object, or
array. Given that underlying assumption - though likely never truly spelled
out anywhere in the documentation - allowing and then throwing a run-time
error when the specific sub-type of json does not match the given operator
makes sense.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-9519-Allows-storing-scalar-json-but-fails-when-querying-tp5795417p5795449.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.