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

user_80869 0
Viewed 163

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

sqlalchemy postgresql json
2 Answers

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.

user_808690

As of sqlalchemy version 1.4.33, you can use the joins_implicitly=True option for table_valued.

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

joins_implicitly – when True, the table valued function may be used in the FROM clause without any explicit JOIN to other tables in the SQL query, and no “cartesian product” warning will be generated. May be useful for SQL functions such as func.json_each().

source

user_45311640
Related