How to write dynamic sql query in JPA to query data from jsonb column?

Question

I want to write a dynamic JPA query to query data from jsonb column?

Example :

 recordid |id | typename | useridentifier | value | tags                                                                                                                                                                             

     4793 | 8d7af416-3940-4b7e-9dbb-972b5a31c4fb | AF.ConnectionRecord | z6MkiXvHoNWQEs9RWpLy8mibb9BBQxrsAqiWbZmmAxNBQurq | {"Id": "8d7af416-3940-4b7e-9dbb-972b5a31c4fb", "Alias": {"Name": "did:dtx:z6Mkw87LmKzcDty6ht5L636kzxHH7DjiTVJuuaGbTMpWYwF9"}, "State": "Connected", "Endpoint": {"uri": "http://127.0.0.1:5000/api/sharedagent/endpoint?identifier=HfrJB5kAtMUdbPEdQU8v9rjHHeTs3c4ZDZMfd5rVdiTm-user", "verkey": ["HfrJB5kAtMUdbPEdQU8v9rjHHeTs3c4ZDZMfd5rVdiTm"]}, "MiscData": {"MyOrgDid": "did:dtx:z6MkiXvHoNWQEs9RWpLy8mibb9BBQxrsAqiWbZmmAxNBQurq", "TheirOrgDid": "did:dtx:z6Mkw87LmKzcDty6ht5L636kzxHH7DjiTVJuuaGbTMpWYwF9"}, "threadId": "f8a815a5-05ab-4198-8613-a57641cab9ca", "CreatedAtUtc": "2021-07-14T05:25:10.7722507", "UpdatedAtUtc": "2021-07-14T05:28:23.752349"} | {"MyVk": "HJaPNSqrJm1yCLbibt5YidnMd7CZtZq4VuJNT1G5uwBq", "MyDid": "did:dtx:z6MkvkqRxh6HeJWSJqSRHT3PZjLMSgURJT5RBvDJHHE6q9yD", "State": "Connected", "TheirVk": "Ec4by1o9UVW91kEGRij5Cs2bSEHY3p8wLE2JMAGe4fwW", "TheirDid": "did:dtx:z6Mkt4KeZG3ap2zc8F4y7Hgv3xabFoZPThPJ2EwEBSEeytit", "threadId": "f8a815a5-05ab-4198-8613-a57641cab9ca", "connectionKey": "SQd4RocaX5QJi9TJ2bgpLht5zU8r5YjwZM1xgV2Dbwn", "~CreatedAtUtc": "637618371107722507", "~UpdatedAtUtc": "637618373037523490", "MultiPartyInvitation": "False", "autoAcceptConnection": "true"}

Now, my query would be

select * from nonsecretrecords where typename = 'AF.ConnectionRecord' 
AND useridentifier = 'z6MkiXvHoNWQEs9RWpLy8mibb9BBQxrsAqiWbZmmAxNBQurq' 
AND tags ->> 'MyVk' = 'HJaPNSqrJm1yCLbibt5YidnMd7CZtZq4VuJNT1G5uwBq' 
AND tags ->> 'connectionKey' = 'SQd4RocaX5QJi9TJ2bgpLht5zU8r5YjwZM1xgV2Dbwn';

Here 'tags' is a jsonb type column and so my query would be like the above one. This is one example query, I want to create a dynamic query, such that the part in the tags (Here, 'MyVk' and 'connectionKey') would be dynamic and can change.

How can I do this using Spring JPA?

Thanks

Hi @nikolai

Can you give more ideas. This is my specification in service :-

public static Specification<NonSecretRecordEntity> findBySpecification(String typename) {
    return (root, query, builder) -> {
        return builder.equal(root.get("typeName"), typename);
    };
}

I want to query according to values that are there in the "tags" column, but I haven't defined a class for that tags column, it is generic using ObjectMapper.

Check this out: https://stackoverflow.com/questions/57351772/build-predicates-for-a-postgres-jsonb-column-with-criteria-builder-using-jpa-cri — Nikolai Shevchenko, Jul 14 '21 at 08:44
Hi @NikolaiShevchenko, I have edited the question. Can you please check and provide more suggestions — Enthusiast Coder, Jul 14 '21 at 10:37

score 0 · Answer 1 · answered Jul 14 '21 at 11:19

The new version of your Specification will look like following:

public static Specification<NonSecretRecordEntity> findBySpecification(
    String typename, 
    String useridentifier, 
    Map<String, String> tags
) {
    return (root, query, builder) -> {
        List<Predicate> predicates = new ArrayList<>();
        predicates.add(builder.equal(root.get("typeName"), typename));
        predicates.add(builder.equal(root.get("useridentifier"), useridentifier));
        tags.forEach((tagName, tagValue) -> {
            predicates.add(builder.equal(
                builder.function("jsonb_extract_path_text", 
                    String.class, 
                    root.get("tags"), 
                    builder.literal(tagName)),
                tagValue
            ));
        });
        return builder.and(predicates.toArray(new Predicate[0]));
    };
}

And be used like this

String typeName = "AF.ConnectionRecord";
String useridentifier = "z6MkiXvHoNWQEs9RWpLy8mibb9BBQxrsAqiWbZmmAxNBQurq";

Map<String, String> tags = new HashMap<>();
tags.put("MyVk", "HJaPNSqrJm1yCLbibt5YidnMd7CZtZq4VuJNT1G5uwBq");
tags.put("connectionKey", "SQd4RocaX5QJi9TJ2bgpLht5zU8r5YjwZM1xgV2Dbwn");

Specification<NonSecretRecordEntity> spec = findBySpecification(typeName, useridentifier, tags);

How to write dynamic sql query in JPA to query data from jsonb column?

1 Answers1