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:

In repository class @Query contains all hardcoded value. How to do them dynamically?
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?

simple google search: baeldung.com/spring-data-jpa-query#indexed-query-parameters — Toerktumlare
– Toerktumlare, Commented Feb 14, 2023 at 22:18

kerbermeister · Accepted Answer · 2023-03-08 14:16:03Z

0

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);

edited Mar 8, 2023 at 14:16

kerbermeister

4,4204 gold badges18 silver badges40 bronze badges

answered Feb 15, 2023 at 2:41

Abdullah Imran

3998 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

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