How can I search a row using a value which is in a jsonB column?

Question

I have been trying this since the whole day but unable to find a solution.

I have a Postgres table items which has a jsonb column named item_stats. It keeps value like

{"barcodes": [{"qty": "1", "code": "3123123123"}, {"qty": "60", "code": "100123123596"}], "barCodeId": "09048921312"}

I am using Spring data JPA in my Spring boot service and just want to get the row when I pass the code 3123123123.

I tried below native query, but it is not replacing the parameter with the actual value.

Below is the query which I have constructed.

public static final String FIND_ITEM_BY_BARCODE = "SELECT * FROM item WHERE item_stats -> 'barcodes' @> '[{\\\"code\\\"\\: ?1}]'";

Below is the Repo call.

@Query(nativeQuery = true, value = MyConstants.FIND_ITEM_BY_BARCODE) ItemEntity findByBarcode(String barcode);

Below is the error that I am getting.

ERROR: invalid input syntax for type json\n Detail: Token "\" is invalid.\n Position: 67\n Where: JSON data, line 1: [{\..."}

Below is the query that the framework is creating.

SELECT * FROM item WHERE item_stats -> 'barcodes' @> '[{"code": ?1}]'

Please help me find out a way to query the jsonB column.

I have looked into Stackoverflow but could not find any question that could help me and hence I am here writing the question.

Thank You for your help.

Answer 1

I was able to find a way to query which was fairly simple. I created a String variable as below.

String jsonStr = "{\"barcodes\": [{\"code\": \""+barcodeValue+"\"}]}";

You can use String format() to format jsonStr and pass barcodeValue. The query that worked for me is shared below:

"SELECT * FROM item WHERE item_stats @> cast(:secondaryBarcode as jsonb)";

The method in repo is kept unchanged. findByBarcode(String secondaryBarcode);

I hope this will help somebody looking out to troubleshoot similar issue.