Embeddable array of objects cannot be searched by properties

[tudddorrr]

Describe the bug
Embeddable arrays cannot be searched in mysql (haven't tested other drivers). I think the mysql driver manually creates a query for searching json objects but doesn't take into account that embeddables can be arrays of objects.

To Reproduce
Create an Embeddable array property in an entity:

@Embeddable()
class Prop {
  @Property()
  key: string
  
  @Property()
  value: string
}

...

@Embedded(() => Prop, { array: true })
props: Prop[] = []

Try to search by a property inside the array of objects:

const { search } = request.query
const players = await em.find({ props: {
  value: {
    $like: `%${search}%`
  }
 })

This will produce an sql query looking something like this:

select `e0`.* from `player` as `e0`
where `e0`.`props`->'$.value' like '%xyz%'

Expected behavior
If the embeddable is set to array mode then the query should use the json array search syntax. I'm not sure if there's any fancy syntax for doing that but currently I'm using the QueryBuilder to manually use json_extract:

em.createQueryBuilder(Player, 'p')
  .where('json_extract(props, \'$[*].value\') like ?', [`%${search}%`])

[mbvissers]

Is there any update on working with an array of embeddables? I'm facing a similar issue.

E: I have been able to resolve my issue by using the last snippet of tudddorrr. It's not the cleanest but it works great that way.

[B4nan]

The problem with this is that only MySQL offers feasible solution, I haven't found a way to do it with other drivers (that would fit how the QB is designed).

[jcrben]

Is it worth submitting feedback upstream to postgres? Curious about the blocker in how postgres works versus MySQL.

[B4nan]

More context here, I got some suggestions on how this could be done, but the problem is that it's not possible to go that way without a lot of added abstractions, or it would be very limited in general.

https://stackoverflow.com/questions/67785282/how-can-i-filter-by-jsonb-object-value-inside-array