How to Join ahoy_events table on properties field

[vikas-patel]

Don't know if it's possible to JOIN 'ahoy_events' with another table ON 'properties' field, because 'properties' is a JSON field.
I am storing another table id in the 'properties' json column of 'ahoy_events', and want to JOIN tables and run a database query.
Sample database query:
ActiveRecord::Base.connection.exec_query('SELECT events.*, COUNT(ahoy_events.id) as visit_count FROM "events" LEFT JOIN "ahoy_events" ON "ahoy_events"."properties.event_uuid" = "events"."uuid" GROUP BY "events"."id"')
This gives error:
ERROR: column ahoy_events.properties.event_uuid does not exist (PG::UndefinedColumn)

I need to display a table containing events attributes and it's ahoy_events counts. If above JOIN database query doesn't work, I would need to create another custom column to ahoy_events table and apply JOIN on it.

[ankane]

Hey @vikas-patel, try using ahoy_events.properties ->> 'event_uuid'. https://www.postgresql.org/docs/current/functions-json.html

Note: ->> returns text, so you may need to cast depending on the field you're trying to join with.

[vikas-patel]

Thanks @ankane, it worked.