0

I want to get event id's for events with property messageUUID equal to passed value from function. metaProperties is a hashmap with keys:

"messageId"
"messageType"
"messageUUID"

and many events will have the same messageId as messageUUID values, and when I try to Query for events where key is messageUUID and value of it is equal to the one I'm searching for - it gives error

  @Query("select event.id from EventLogDb event where (key(event.metaProperties) = 'messageUUID' and :message in VALUE(event.metaProperties))")
  Slice<EventLogDb> findAllByMessage(
          String message, Pageable pageable);

ERROR: more than one row returned by a subquery used as an expression

So as far as I understand - it's because :message in VALUE(event.metaProperties) gives two results - for keys called messageId and messageUUID, as they have the same values.

How should I correct this Query to get it working properly?

FilipA
  • 526
  • 3
  • 10

1 Answers1

0

You are getting ERROR: more than one row returned by a subquery used as an expression because as you said there are many rows with the same messageUUID, thus when you use "=" sign query expects exactly 1 value to be returned, and in your case there are 2,3,... values for one entity -> more than row returned (which messageUUID do you really want against given metaPropeties ?)

Try something like this:

@Query("select event.id from EventLogDb event where event.metaProperties IN :messageUUID and event.metaProperties in :message")

NOTE - IN operator should not be used if you have NULL values

Or check (restructure) DB model

Maybe this can help: Solution to "subquery returns more than 1 row" error

Creed21
  • 67
  • 7