Automatically convert Records to json

[lsabi]

I've already checked the following issues, but none of them work for my case
#17
#263

Basically, I'm fetching several records for an API that should return a JSON response. The code I'm using is the following

async with db_pool.acquire() as conn: async with conn.transaction(): res = await conn.fetch(query, param) return res
Even by adding res = [dict(r.items()) for r in res] the response is not correct for javascript that has to fetch from the client side the JSON data.

How can I correctly transform the resulting list of records into a valid JSON? I haven't found anything in the docs.

No, I'm not using sqlalchemy or similars. They add too much overhead.

Thanks.

[wnklb]

You need to encode it as json. Either do it explicitly via json.dumps() or have a look at your chosen web framework if it supports this.

I'm using tornado's json_encode() which comes down to this: json.dumps(values).replace("</", "<\\/")

async with self.application.db.acquire() as conn:
    query = "SELECT * from ..."
    records = await conn.fetch(query)
    values = [dict(record) for record in records]
    result = json.dumps(values).replace("</", "<\\/")
    self.write(result)

Hope this helps, cheers!

[lsabi]

With a play dump i get TypeError: Object of type datetime is not JSON serializable .

How well does this approach perform for huge results?

Thanks

EDIT

Ok, I managed to overcome the issue, though it's not optimal. It would be an interesting feature to directly have JSON result instead of a list of records which have to be serialized into JSON

[elprans]

I would recommend server-side JSON serialization using json_agg and various json_build_* functions and then fetch that with fetchval(). The reference is here: https://www.postgresql.org/docs/current/functions-json.html

[lsabi]

But I don't have JSON values in my database. I use JSON as a mean to transfer data from my api to the frontend. That's why I would like to directly return the result of the query, limiting the overhead of converting the list of records into a list of dictionaries and then returning it.

[elprans]

The JSON support is not limited to JSON values, you can serialize arbitrary data to JSON in Postgres.

[lsabi]

But then also the asyncpg result is a json? Or will it still be incapsulated into Record instances?

[elprans]

If you use json_agg() in the query and then connection.fetchval() you'll get the JSON result directly

[mecampbellsoup]

If you use json_agg() in the query and then connection.fetchval() you'll get the JSON result directly

For a query shaped like the following:

    SELECT json_build_object(
        'id', u.id,
        'first_name', u.first_name,
        'last_name', u.last_name,
        'email', u.email,
        'date_joined', u.date_joined
    ) AS user,
        json_build_object(
        'id', o.id,
        'name', o.name,
        'credit_card_id', o.credit_card_id
    ) AS organization
    FROM iam_user u
        LEFT JOIN organization o ON u.email = o.owner_id
    WHERE u.email = :email;

... using fetchval doesn't work well since I have a multi-keyed JSON object and it will default to just returning the user data via column=0.

However using fetchrow it seems like I incur an additional serialization in Python because I have to do dict(conn.fetchrow(query)).

I'd really love to avoid an extra serialization in Python since my query returns serialized JSON - any suggestions @elprans? cc @lsabi in case you were doing something similar (unclear from your original question though).