Query a jsonb column dynamically in Postgres

Question

I have a column carInfo with type jsonb in one of my tables in a PostgreSQL database. A sample row looks something like this:

{
   "name":"John",
   "age":25,
   "car":{
      "brand":"KIA",
      "year":2015
   }
}

I know that in PostgreSQL you can query it in this way:

select * from car where carInfo -> 'name' = 'John'

but I would like to make this query dynamic (by using PostgreSQL functions or something) so that I can query it from my Java application. I want to reuse the same query even when I want to go a level deeper for example

select * from car where carInfo -> 'car' -> 'brand' = 'KIA'

Any idea how I can achieve this?

score 3 · Accepted Answer · 2020-07-14 13:21:18Z

3

You can use the contains operator @>

select *
from car
where car_info @> '{"name": "John"}';

or

select *
from car
where car_info @> '{"car": {"Brand": "KIA"}}';

In your Java code you can write a single PreparedStatement to deal with both:

String sql = "select * from car where car_info @> cast(? as jsonb)";
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setString(1, "{\"car\": {\"Brand\": \"KIA\"}}";
ResultSet rs = pstmt.executeQuery();

Using the @> operator has the added benefit, that Postgres can make use of a (GIN) index on that column, which would not be possible with the -> chain.

Collectives™ on Stack Overflow

Query a jsonb column dynamically in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related