# Suranyami

## 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 postgres:

SELECT
CAST(data ->> 'boxes' AS INTEGER) as boxes,
data ->> 'storeid' AS storeid,
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',
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 ecto:

  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',

The 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.