0

When interacting directly with a sql db (that contain a jsonb column), the query required is:

SELECT stuff FROM alerts WHERE id = '12345'
AND (stuff->'action'->>'actionId' = '123')
ORDER BY (stuff->'action'->>'timestamp');

The 'stuff' column is jsonb format with nested properties, which is being filtered to only return certain results (actionId = 123 for example).

I now want to access this using Spring Data JPA. The ability to manipulate a retrieved json with certain parameters being replaced in the query (id and actionId) is the desired outcome.

The repository interface which extends JpaRepository can have methods with no body declared, and @Query annotation used on that method. However @Query annotation doesn't support searching within the json for certain properties. Specifically, the -> operator isn't recognized, although this may be a syntax issue.

Is there a standard practice for this type of retrieval of data from sql (that contain a json column) databases?

regamblr
  • 107
  • 9
  • 1
    From what database query language is `stuff->'action'->>'actionId' = '123'` ... or is that something you made up? – Jim Garrison Oct 31 '22 at 17:33
  • it's postgres sql, and it works. It is used to query a column which is jsonb. I know it isn't strict sql, but it also isn't strictly nosql from my limited understanding @JimGarrison – regamblr Oct 31 '22 at 19:27
  • 1
    This answer may help you, https://stackoverflow.com/questions/43900457/how-do-i-use-spring-data-jpa-to-query-jsonb-column – Adisesha Nov 01 '22 at 07:32

0 Answers0