I have a table, persons
, which contains two columns, an id
and a JSONB-based data
column (this table has just been made for demonstrational purposes to play around with PostgreSQL's JSON support).
Now, supposed it contains two records:
1, { name: 'John', age: 30 }
2, { name: 'Jane', age: 20 }
Now, supposed I want to get the name of every person older than 25. What I have tried is:
select data->'name' as name from persons where data->'age' > 25
Unfortunately, this results in an error. I can solve it by using ->>
instead of ->
, but then comparisons don't work as expected any more, since not the numbers are compared, but their representations as strings:
select data->'name' as name from persons where data->>'age' > '25'
I then figured out that I can actually solve the issue by using ->
and a cast to int
:
select data->'name' as name from persons where cast(data->'age' as int) > 25
This works, but it's not that nice that I have to know the actual type (the type of age
in the JSON document is number
anyway, so why can't PostgreSQL figure out that by itself?).
I then figured out that if I manually convert to text
using the ::
syntax, everything works as expected, too – although we are now comparing strings again.
select data->'name' as name from persons where data->'age'::text > '25'
If I then try this with the name instead of the age, it doesn't work:
select data->'name' as name from persons where data->'name'::text > 'Jenny'
This results in an error:
invalid input syntax for type json
Pretty obviously, I don't get something here. Unfortunately, it's quite hard to find any real-world examples of using JSON with PostgreSQL.
Any hints?
Best Answer
This does not work because it's trying to cast a
jsonb
value tointeger
.This would actually work:
Or shorter:
And this:
Seems like confusion with the two operators
->
and->>
and operator precedence. The cast::
binds stronger than the json(b) operators.Figure out type dynamically
This is the more interesting part of your question:
SQL is a strictly typed language, it does not allow the same expression to evaluate to
integer
in one row and totext
in the next. But since you are only interested in theboolean
result of the test, you can get around this restriction with aCASE
expression that forks depending on the result ofjsonb_typeof()
:An untyped string literal to the right of the
>
operator is coerced to the respective type of the value to the left automatically. If you put a typed value there, the type has to match or you have to cast it explicitly - unless there is adequate implicit cast registered in the system.If you know that all numeric values are actually
integer
, you can also: