Fun with Postgres JSON queries and Ecto
Had an unusual sort of query where we had a stream of sales data that was cumulative for the day but sent through in updates, so had to total them for a given store, for all users, but for the last entry on the day. And it was stored in Postgres in a JSON blob.
Here's how I ultimately solved it in
SELECT CAST(data ->> 'boxes' AS INTEGER) as boxes, data ->> 'storeid' AS storeid, data ->> 'username' AS username, CAST(inserted_at AS date) AS inserted_date FROM external_records WHERE api_id = 6 AND data ->> 'transactiondate' IS NOT NULL GROUP BY data ->> 'storeid', data ->> 'username', CAST(inserted_at AS date), inserted_at, data ORDER BY inserted_at DESC;
I've been using Beekeeper Studio for running SQL queries. It's so much nicer than PGAdmin.
And here it is in
def last_for_store_on_day(api_id, storeid, date_string) do date = Date.from_iso8601!(date_string) IO.puts(date) query = from(ex in ExternalRecord, where: ex.api_id == ^api_id, where: fragment("CAST(inserted_at AS DATE)") == ^date, where: fragment("data ->> 'storeid'") == ^storeid, group_by: fragment(""" data ->> 'storeid', data ->> 'username', CAST(inserted_at AS date), inserted_at, data, id """), order_by: [desc: ex.inserted_at] ) Repo.all(query) end
fragment expressions are a little odd at first, but I like this a lot more than just doing the entire block as SQL, if only for security purposes.