How to make sqlachemy see implicit lateral joins as in json_each or jsonb_each?

Question

I'm trying to figure out the proper way of using json_each. I've seen some tricks like using column or text. So far I've found a quite clean way using table_valued, that works except for the cross join warning.

term = 'connection'
about_exp = func.json_each(EventHistory.event, '$.about').table_valued('value')
events = s.query(EventHistory).filter(about_exp.c.value == term)

EventHistory contains one json field that looks like this: {"about": ["antenna", "connection", "modem", "network"]}

The resulting query works as expected but I'm getting the following warning: SAWarning: SELECT statement has a cartesian product between FROM element(s) "event_history" and FROM element "anon_1". Apply join condition(s) between each element to resolve.

For any one that would like to experiment here is a working example in from of unit tests: https://gist.github.com/PiotrCzapla/579f76bdf95a485eaaafed1492d9a70e

Answer 1

So far the only way I found not to emit the warning is to add join(about_exp, true())

    from sqlalchemy import true

    about_exp = func.json_each(EventHistory.event, '$.about').table_valued('value')
    events = s.query(EventHistory).join(about_exp, true()).filter(
      about_exp.c.value == about_val
    )

But it needs additional import of true and additional join statement, if anyone has a better solution please let me know.