How to query jsonb datatype (Postgresql) in java code using Spring webflux & R2dbc

Question

I have postgresql db with below structure

CREATE TABLE products (
    id bigserial,
    name varchar(30) NOT NULL,
    owner_id bigint,
    pdata jsonb NOT NULL,
    PRIMARY KEY (id)
);

Here pdata is of jsonb datType

exmaple of pdata(jsonb dataType)

  "facility": {
    "parent": {
      "type": "City"
    },
    "facilityId": "Z00TRIZR6KAQ6"
  }
} 

I I run the below query from PGadmin it works fine. I get the desirable result

from techwriting.products
where pdata ->'facility' ->'parent'->>'type'='City'
and pdata ->'facility' ->>'facilityId'='Z00TRIZR6KAQ6';

Basically above query is checking various attributes in jsonb value and giving the result.

I am running the same query in java code using spring webflux and R2dbc. I am able to get the same response in java.

Model class

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Table("products")
public class Product {

  @Id
  private Long id;

  private String name;

  private  String pdata;

  @Column("owner_id")
  private long ownerId;

}

Repository class

import com.maersk.jsonb.model.Product;
import org.springframework.data.r2dbc.repository.Query;
import org.springframework.data.repository.reactive.ReactiveCrudRepository;
import reactor.core.publisher.Flux;

public interface ProductRepository extends ReactiveCrudRepository<Product, Long> {

  @Query(value="select * from techwriting.products where pdata ->'facility' ->'parent'->>'type'='City' and pdata ->'facility' ->>'facilityId'='Z00TRIZR6KAQ6'")
  Flux<Product> findAllByName();
}

Response: We are getting below response. Response is correct with same no of records .

[
    {
        "id": 1,
        "name": "xyz",
        "pdata": "{\"facility\": {\"parent\": {\"type\": \"BBSR\"}, \"facilityId\": \"Z00TRIZR6KAQ6\"}}",
        "ownerId": 2
    }
]

My Question are:

1. In repository class @Query contains all hardcoded value. How to do them dynamically?

2. Response contains pdata as String. However json data stored in postgresql and what I am getting the structure is different.

pdata(jsonb type) in postgresql:

{
  "facility": {
    "parent": {
      "type": "BBSR"
    },
    "facilityId": "Z00TRIZR6KAQ6"
  }
}

getting in response:

"{\"facility\": {\"parent\": {\"type\": \"BBSR\"}, \"facilityId\": \"Z00TRIZR6KAQ6\"}}"

How can we convert String to as stored in db as shown above?

Answer 1

My answer to Q1.

@Query(value="select * from techwriting.products where pdata ->'facility' ->'parent'->>'type'=$1 and pdata ->'facility' ->>'facilityId'=$2")
Flux<Product> findAllByfacilityIdandtype(String type, String facilityId);