Rails/Postgres nested JSON query

Viewed 1688

I have a JSON column in my events table called payload. I can create an event like this:

Event.create!(application_id: 1, stage: 'INITIATION', payload: { key: 'OUTCOME', value: {school_id: 2, prefered_language: 'GBP'}})

Simple queries like

Event.where("payload->>'key' = ?", "OUTCOME")

Will work fine, but how can I then add extra filters with the JSON that's nested in the value portion

 Event.where("payload->>'key' = ? AND payload ->>'value'->>'school' = ?", "OUTCOME", 2) 
                                                 ^^^^^^^^^^^^^^^^^^^^

I tried that but i got:

PG::UndefinedFunction: ERROR:  operator does not exist: text ->> unknown
LINE 1: ...ad ->>'key' = 'INPUTPARAMS' AND payload ->>'value'->>'school...                                                         ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

How do I get into the school attribute within value?

I have taken the idea from this documentation on ActiveRecord and JSON

1 Answers
Related