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-14T19:02:08.107

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 '16 at 19:02

answered Mar 14 '16 at 18:56

Lukasz Szozda

162,964
23
234
275

@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. – Lukasz Szozda Mar 14 '16 at 19:07
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 Mar 14 '16 at 19:31
but using this code I got another exception: org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111 – Shvalb Mar 14 '16 at 19:31
@Shvalb What kind of do exception you get? – Lukasz Szozda Mar 14 '16 at 19:33
org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111 – Shvalb Mar 14 '16 at 19:33
@Shvalb `SELECT id` and check for each column. You probably have bad mapping. – Lukasz Szozda Mar 14 '16 at 19:35
I changed the query to do : select * ... but same exception. – Shvalb Mar 14 '16 at 19:36
@Shvalb `SELECT name` and so on. `*` changes nothing. Don't be lazy. Check which column causes problem. – Lukasz Szozda Mar 14 '16 at 19:36
You're probably right about the JSON. It worked when I mapped single column!! so now I will deal with the JSONs. – Shvalb Mar 14 '16 at 19:43

PostgreSQL: Query for specific field in json column

1 Answers1