How to get value of nested object of postgres jsonb column in spring boot

Question

I have used vlad mihalcea's dependency to store json value in table.

Table name: valuation_report JsonbColumn is parameters attribute name in pojo is params

Able to get value of single object like address="Address1"

{"address":"Address1","nestedObj":{"firstName":"Sanjay"}}

To get address I've implemented specification and @Overriden Predicate method like

@Override
public Predicate toPredicate(Root<ValuationReport> root, CriteriaQuery<?> query, CriteriaBuilder cb)
{
return cb.equal(cb.function("jsonb_extract_path_text", String.class,root.<String>.get("params"),cb.literal(this.locale)), this.fieldToSearch);
}

But now i want to find value of NestedObj like nestedObj's firstName is Sanjay.

{"nestedObj":{"firstName":"Sanjay"}}

Please help me😅 And I've written all this with my mobile so sorry for bad format of question😅.

I'm new to stack overflow guys please help😅

Sanjay Sahani
– Sanjay Sahani

2020-02-11 06:24:38 +00:00
Commented Feb 11, 2020 at 6:24 — Sanjay Sahani
– Sanjay Sahani, Commented Feb 11, 2020 at 6:24

Rahul Gupta · Accepted Answer · 2021-01-20 03:43:02Z

4

Here is your JPA specification.

public Specification<ValuationReport> getFirstNameSpecification(
  final String param, final String subParam, final String value) {
return (root, query, cb) ->
    cb.equal(
        cb.function(
            "jsonb_extract_path_text",
            String.class,
            root.get("params"),
            cb.literal(param),
            cb.literal(subParam)),
        value);
}

Thanks

answered Jan 20, 2021 at 3:43

Rahul Gupta

511 bronze badge

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

Comments

Harshad · Accepted Answer · 2020-02-12 04:30:15Z

3

I have done this with @Query annotation but there's still a way to do with criteria also. I will show my query to do this

@Query(value = "select vr FROM ValuationReport vr  where jsonb_extract_path_text(vr.params,:subParam ,:key)=:value")
    List<ValuationReportJSON> getEntities(@Param("subParam") String subParam,@Param("key") String key,@Param("value") String value);

Hope this works.

answered Feb 12, 2020 at 4:30

Harshad

681 silver badge6 bronze badges

2 Comments

Sanjay Sahani Over a year ago

Thanks man this query worked. Im still eager to do the same using JPA or something

Raja Anbazhagan Oct 6 at 10:37

is the jsonb_extract_path_text available for other database?

Aniket Patil · Accepted Answer · 2020-02-12 04:41:52Z

2

This HQL query works the same way for me (for simple approach).

public void someFunction(String searchKey){
Query query="select obj FROM ValuationReport obj where jsonb_extract_path_text(obj.params,:subParam ,:key)=:value")
Query<ValuationReportJSON> parameters=session.createQuery(query).setParameter("value",searchKey);
List<ValuationReportJSON> resultset=parameters.getResultList();
}

Hope it helps !

answered Feb 12, 2020 at 4:41

Aniket Patil

1371 silver badge7 bronze badges

Collectives™ on Stack Overflow

How to get value of nested object of postgres jsonb column in spring boot

3 Answers 3

Comments

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Linked

Related