CODEWITHSUNDEEP
jsonpostgresqljdbc
Mike Thomsen
Mike Thomsen

Reputation: 37506

How to identify JSON field as JSON type w/ JDBC

I'm experimenting with Postgres support for json, and I can't figure out if there is a way to have JDBC identify it as json. I don't expect the result set to be able to return a nested Map or something like that. I'm just looking for a way to tell from the result metadata or something like that that it's not really a text or string field.

Upvotes: 3

Views: 698

Answers (1)

George S
George S

Reputation: 2151

Oof, it looks like JDBC will tell you it's a string even with a JSON column type in the db. You could:

  1. Use the ResultSetMetaData.getColumnTypeName() method, which will return the db-specific type name and will indicate JSON, hstore, JSONB, or whatever db-specific column type that will be forced into a string.

  2. Try to parse it as JSON and if it succeeds the treat it as JSON. This is obviously suboptimal as you wouldn't know if every value in the column is JSON or not.

  3. If you're pulling a column value directly, you can get the column type directly from the database by using the JDBC connector to query select * from information_schema.columns where table_name = 'table_name'

  4. You can use the JDBC connector to add a column pg_typeof("column_name") as column_name_type in the resultset, e.g. select *, pg_typeof("column_name") as column_name_type from table_name. This would also work if your result set has a calculated column rather than pulling information directly from the database.

The last two can also be useful for dealing with PostgreSQL's hstore or other datatypes that when they pass through JDBC are considered as strings.

Upvotes: 2

Related Questions