imprest

How to get json result as raw binary from Postgres Query

I would like the resulting json from Postgrex query; to not be decoded by Jason.

Instead let it return as a binary / string and sent straight as a response; to avoid the decoding from db and then encoding when sending to client.

Below code does not work. Currently sending it by using

Jason.encode!(List.flatten(result.rows))"

query = "select array_to_json(array_agg(customers)) from customers"
{:ok, result} = Ecto.Adapters.SQL.query(Repo, query, [])

conn
|> put_resp_content_type("application/json")
|> send_resp(200, List.flatten(hd(result.rows)))

15 comments

/phoenix #ecto #postgrex

0 4602 15

2018-11-14 22:27:03 UTC

Marked As Solved

michalmuskala

If you’re using jason, you can use Jason.Fragment.new(iodata) which was designed especially for that use case. The docs are… a bit lacking .

This basically implements exactly what I described in my blog post about doing something similar in Poison.

Post #13

Also Liked

imprest

Taking @michalmuskala, @sribe and @hauleth advise and
looking around the inter-webs; came up with the below examples.
Hope it helps others.

# customer_controller.ex
def index(conn, _params) do
  query = "SELECT json_agg(customers)::text FROM customers"
  result = Repo.query!(query, [])

  conn
  |> put_resp_content_type("application/json")
  |> send_resp(200, ["{\"list\":", result.rows, "}"])
end

More complex example; return an invoice as json object with customer details and list of items bought

# invoice_controller.ex
def show(conn, %{"id" => id}) do
  query = """
  SELECT row_to_json(invoice)::text
  FROM (
    SELECT *,
    (
      SELECT row_to_json(c)
      FROM (
        SELECT *
        FROM customers
        WHERE invoices.customer_id = customers.id
      ) c
    ) AS customer,
    (
      SELECT json_agg(i)
      FROM (
        SELECT id, product_id, qty, rate, total
        FROM invoice_details
        WHERE invoice_id = $1::bigint
      ) i
    ) AS items
    FROM invoices
    WHERE id = $1::bigint
  ) invoice;
  """

  result = Repo.query!(query, [id])

  conn
  |> put_resp_content_type("application/json")
  |> send_resp(200, ["{\"data\":", result.rows, "}"])
end

Post #5

michalmuskala

Also, you don’t need the List.flatten(hd(result.rows)) part. The body passed to send_resp can be iodata, which, among others, means a nested list of binaries. send_resp(200, result.rows) should work just fine.

Also using SELECT json_agg(customers)::text FROM customers might improve things on the DB side.

Post #3

hauleth

Have you tried casting in the DB? Like:

query = "select array_to_json(array_agg(customers))::text from customers"
{:ok, result} = Repo.query(query, [])

conn
|> put_resp_content_type("application/json")
|> send_resp(200, List.flatten(hd(result.rows)))

Post #1

Last Post!

sribe

Yes. My hack is not a lot of code, nor complicated, but it hardwires an assumption of JSON into Phoenix at an inappropriate place. Jason.Fragment.xxx is an appropriate API for decoupling that so that encoders for other formats could be hooked in.

Post #15