CODEWITHSUNDEEP
postgresqlcasting
Aviad Nissel
Aviad Nissel

Reputation: 357

PostgreSQL - JSONB in WHERE

I have a field that is defined to be JSONB, though in reality it has just text (no, I can't change the type as a solution).

This query:

SELECT value FROM property WHERE id = 1;

returns this:

-[ RECORD 1 ]--
value | "IP"

Now, I want to query by this value e.g. SELECT value FROM property WHERE value = 'IP'. I tried several different casting (value::TEXT = 'IP', value::VARCHAR = 'IP') but they all return no results. What is the correct way to do this query?

Upvotes: 0

Views: 62

Answers (2)

freakish
freakish

Reputation: 56467

SELECT value
FROM property
WHERE value = to_jsonb('IP')

If you prefer casting value to string then you have to fix your right side and pass "IP" instead of IP. However if the right side is actually not fixed (i.e. values other than IP are possible) then you would have to properly escape it, e.g. for A"B the actual value would be "A\"B". Thus I suggest my original solution (which takes care of that).

Upvotes: 1

user330315
user330315

Reputation:

Converting a scalar JSON value to text, isn't unfortunately as straight-forward as one might wish. Using to_jsonb() on the right hand (as shown in frekish's answer) side is probably the best solution.

If you are looking for alternatives, you can convert the JSON value to text, using the #>> operator.

select value
from property
where value #>> '{}' = 'IP'

'{}' essentially means "the top level value"

The problem with casting such a value to text is that the double quotes are retained. Another option would therefor be:

select value
from property
where trim('"' from value::text) = 'IP'

Upvotes: 0

Related Questions