PostgreSQL: Query for specific field in json column

Question

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.

Lukasz Szozda · Accepted Answer · 2016-03-14 19:02:08Z

1

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 ║
╚═════════╩══════╩═══════════════════════════════╩═════════════════════════════╩════════╝

edited Mar 14, 2016 at 19:02

answered Mar 14, 2016 at 18:56

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Lukasz Szozda Over a year ago

@Shvalb It should. Didn't you even try it? I have not environment set up for testing it. But it is clearly problem with query.

Shvalb Over a year ago

I tried it and I had to change a bit the syntax to: CAST(expected->>'league_id' as numeric) because ::int would confuse hibernate.

Shvalb Over a year ago

but using this code I got another exception: org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111

Lukasz Szozda Over a year ago

@Shvalb What kind of do exception you get?

Shvalb Over a year ago

org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111

|

Collectives™ on Stack Overflow

PostgreSQL: Query for specific field in json column

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related