0

For example, if I have a JSONB column, and it is stored as:

["oranges","apples","bananas"]

How can I check using a LIKE type search if the substring "app" is in the list?

Ideally I would like be able to do something like:

Basket['fruits'].contains_like('%app%')

Is this possible with sqlalchemy?

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
john
  • 33,520
  • 12
  • 45
  • 62
  • Have a look at https://stackoverflow.com/questions/54669900/sqlalchemy-filter-nested-jsonb-within-arrays, it should be fairly straightforward to adapt to your situation. – Ilja Everilä Jun 29 '19 at 10:32

1 Answers1

0

Using inspiration from the pure PostgreSQL answer here, you can create the following condition:

from sqlalchemy import exists, func, literal_column

exists().select_from(
  func.jsonb_array_elements_text(Basket['fruits']).alias('fruit')
).where(literal_column('fruit').like('%app%'))

The answer raises the issue of performance and potentially adding an index in special cases (when you're only interested in strings starting with a certain value). Have a look if that applies to you and if you experience performance issues

Ruben Helsloot
  • 12,582
  • 6
  • 26
  • 49
  • See Postgres Documentation at https://www.postgresql.org/docs/11/datatype-json.html then section 8.14.3. jsonb Containment and Existence – Belayer Jun 30 '19 at 02:01
  • @Belayer I read the documentation you posted, but I don't understand why you included it. This is very useful for full string matches, but not so much for the partial string matches the original question is about – Ruben Helsloot Jun 30 '19 at 09:31