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.
"#>>" returns text directly, just like ->> vs ->.
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 you put the (data #> '{"a"}')::text construct in the select, you can see what is going on. pulling out the element as JSON quotes the value (if it not a number), because that is what JSON values are supposed to be, and then converting to text leaves the quotes in place. So you are comparing the 3 character '"b"' to the one character string 'b', and they are not equal. If you use #>>, it is pulled out as text in the first place and the quotes are not put on there.
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 :)
I don't really have an opinion on that, it just isn't obvious which way is better--I can see times I would want either one. There are people who have thought about this much more deeply than I have, but they haven't shown up on this thread yet. (I think they are too busy over on the hackers list, arguing over what behavior the next generation of json operators should have.)
You can create a new operator with the behavior you want. I would like some simple notation one could add to an operator or function invocation which means "catch errors and convert to null", as I have several plperl functions which I have created in two forms, one with an eval block and one without. It would be nice to have one function with a run-time notation to distinguish the behavior.