How to filter on JSON column

[n4rk0o]

Hello,

I started using Piccolo ORM and everything is working like a charm except for one thing. I'm trying to filter objects by using the Where clause but I'm unable to find a way to filter on a JSON column and more precisely on a specific key (like {'name': 'Paris'})

Is is possible and if yes how can I do that?

Thanks a lot and keep continue you are doing a geat job .

[dantownsend]

@n4rk0o Thanks for the kind words.

For filtering JSONB columns, you can try the arrow function.

For a table like this:

class RecordingStudio(Table):
    facilities = JSONB()

You can do:

await RecordingStudio.select().where(RecordingStudio.facilities.arrow('foo') != None)

This doesn't work on JSON columns at the moment. - for JSON columns you can use WhereRaw.

from piccolo.query import WhereRaw

await RecordingStudio.select().where(WhereRaw("facilities->>'some key' IS NOT NULL"))

[n4rk0o]

Hello @dantownsend

Thanks for the quick update I appreciate.

[n4rk0o]

Hello @dantownsend could you explain me how to filter on a sub key on a JSONB object?

Example:
Column name is "animal"
Value: {"dog": {"race":"labrador"}}

I need to filter on race key.

I tried with arrows but it's failing.

Thanks for your help 🙂

[n4rk0o]

I finally found how to do:

from piccolo.query import WhereRaw

await RecordingStudio.select().where(WhereRaw("facilities->'some key'->'sub key'->>'final_key' IS NOT NULL"))