CODEWITHSUNDEEP
javahibernatepostgresql
Shvalb
Shvalb

Reputation: 1933

PostgreSQL: Query for specific field in json column

I have a DB table that has a column named 'expected' which defined as json.

and one of the rows in the db has the following value:

expected = {"min":5, "max":20, "league_id":7}

I'm trying to select this row using Hibernate as follow:

Object[] league = (Object[])session.createSQLQuery(
                "SELECT id, name, type, expected, content " +
                "FROM bonus " +
                "WHERE CAST(expected->>league_id as numeric)=7")
                .uniqueResult();

I get the following exception:

[SqlExceptionHelper] - ERROR: column "league_id" does not exist

Why it tried to translate league_id into a column?

by the way, if I try this query in my pgAdmin tool it works perfectly!

Any help would be appreciated! Thanks.

Upvotes: 1

Views: 3704

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

You should quote league_id with ':

SELECT id, name, type, expected, content
FROM bonus
WHERE (expected->>'league_id')::int = 7;

SqlFiddleDemo

JSON Functions and Operators:

╔═════════╦══════╦═══════════════════════════════╦═════════════════════════════╦════════╗
║ Operator║ Type ║         Description           ║          Example            ║ Result ║
╠═════════╬══════╬═══════════════════════════════╬═════════════════════════════╬════════╣
║ ->>     ║ text ║ Get JSON object field as text ║ '{"a":1,"b":2}'::json->>'b' ║      2 ║
╚═════════╩══════╩═══════════════════════════════╩═════════════════════════════╩════════╝

Upvotes: 1

Related Questions