Query jsonb array for integer member

Viewed 2922

Background: We use PaperTrail to keep the history of our changing models. Now I want to query for a Item, which belonged to a certain customer. PaperTrail optionally stores the object_changes and I need to query this field to understand, when something was created with this ID or changed to this ID.

My table looks simplified like this:

item_type | object_changes
----------|----------------------------------------------------------
"Item"    | {"customer_id": [null, 5], "other": [null, "change"]}
"Item"    | {"customer_id": [4, 5], "other": ["unrelated", "change"]}
"Item"    | {"customer_id": [5, 6], "other": ["asht", "asht"]}

How do I query for elements changed from or to ID 5 (so all rows above)? I tried:

SELECT * FROM versions WHERE object_changes->'customer_id' ? 5;

Which got me:

ERROR:  operator does not exist: jsonb ? integer
LINE 1: ...T * FROM versions WHERE object_changes->'customer_id' ? 5;
                                                                 ^
HINT:  No operator matches the given name and argument type(s).
You might need to add explicit type casts.
2 Answers
Related