0

I have a table Employee with fileds id, name, age , Address(as Jsonb string stored in Postgres DB)

@Entity
@Table(name = "Employee")
@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
public class Employee  implements Serializable {
@Id
@Column
@GeneratedValue(strategy = GenerationType.AUTO)
int Id;
@Column
String name;
@Column
int age;

@Column(columnDefinition = "jsonb")
@Type(type="jsonb")
Address address;   // this field is JSONB data stored in postgress DB

// setter and getters

}

public class Address implements Serializable {

String flatNo;
String area;
String state;

// setter and getters
}

DB table data format:

id |   name   | age |                        address  
---+----------+-----+--------------------------------------------------------
20 | Peter    |  16 | {"area": "KKR", "state": "KARNATAKA", "flatNo": "456"}
21 | Nani     |  20 | {"area": "CTM", "state": "TA", "flatNo": "111"}

My query here is: I need to filter data based on jsonb field.

Like need employee full row details based on area="KKR", as area is not direct field of employee, I'm unable to filter data. I'm new to Postgres. Can you please suggest me what to do?

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
user3347069
  • 11
  • 3

1 Answers1

1

In SQL you can do this using the @> operator:

select *
from employee
where address @> '{"area": "KKR"}';

Alternatively with the ->> operator that returns a single value for a key:

select *
from employee
where address ->> 'area' = 'KKR';