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
After much searching, I came across this article.
The #>>
operator allows you to dig inside the JSON. My query could be fixed like this.
Event.where("payload->>'key'= ? AND payload#>>'{value, school_id}' = ?", 'OUTCOME', shcool_id)
You could theoretically go down further by adding each layer to the predicate block {value, school, next_level, and_so_on}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With