Re: BUG #9519: Allows storing scalar json, but fails when querying - Mailing list pgsql-bugs
| From | Alf Kristian Støyle |
|---|---|
| Subject | Re: BUG #9519: Allows storing scalar json, but fails when querying |
| Date | |
| Msg-id | CA+tXr-9JSk5YruYQGuYBUo0kbBh_gJoAoi_GnouehKRMvwAaSw@mail.gmail.com Whole thread Raw |
| In response to | Re: BUG #9519: Allows storing scalar json, but fails when querying (Jeff Janes <jeff.janes@gmail.com>) |
| Responses |
Re: BUG #9519: Allows storing scalar json, but fails when querying
(David Johnston <polobo@yahoo.com>)
Re: BUG #9519: Allows storing scalar json, but fails when querying (Jeff Janes <jeff.janes@gmail.com>) |
| List | pgsql-bugs |
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)
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)
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
Alf
On 10 March 2014 22:42, Jeff Janes <jeff.janes@gmail.com> wrote:
On Mon, Mar 10, 2014 at 8:09 AM, <alf.kristian@gmail.com> wrote:The following bug has been logged on the website:
Bug reference: 9519
Logged by: Alf Kristian Støyle
Email address: alf.kristian@gmail.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 scalarI 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
pgsql-bugs by date: