Re: BUG #9519: Allows storing scalar json, but fails when querying
От | David Johnston |
---|---|
Тема | Re: BUG #9519: Allows storing scalar json, but fails when querying |
Дата | |
Msg-id | 1394485742473-5795449.post@n5.nabble.com обсуждение исходный текст |
Ответ на | Re: BUG #9519: Allows storing scalar json, but fails when querying (Alf Kristian Støyle <alf.kristian@gmail.com>) |
Ответы |
Re: BUG #9519: Allows storing scalar json, but fails when
querying
|
Список | pgsql-bugs |
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.
В списке pgsql-bugs по дате отправления: