PostgreSQL with asyncpg return a prefix string for a json_agg() select

Question

I'm trying to use a json_agg() function of PostgreSQL with python and asyncpg. The data returned is a list of just one element that begins with <Record jsonb_agg= string and than a correct json.

I'd like to have the json string directly from the DB and not use the "workaround" in the code. Is something wrong in my code?

"""Example PostgreSQL JSON with asyncpg"""

import asyncio
import json
import asyncpg
from pydantic import BaseModel, ValidationError

DSN = "postgresql://postgres:[email protected]/testdb"
QUERY = 'select jsonb_agg(t) from (select * from "tmp") t;'


async def fetch_data_from_db():
    """get data from DB."""
    conn = await asyncpg.connect(DSN)
    result = await conn.fetch(QUERY)
    await conn.close()
    return result


async def get_list():
    """Get List"""
    result = await fetch_data_from_db()
    return result


class Person(BaseModel):
    """Person model."""
    id: int
    name: str
    surname: str

    @classmethod
    def parse_from_json(cls, json_data):
        """Parse from json."""
        try:
            return cls(**json.loads(json_data))
        except ValidationError as e:
            raise ValueError(f"Error: {str(e)}")


async def main():
    result_raw = await get_list()
    ### this is a workaround ###
    element = result_raw[0]
    json_string = element["jsonb_agg"]
    json_result = json.loads(json_string)
    ############################

    print(f"type and len of result_raw: {type(result_raw)}/{len(result_raw)}")
    print(f"type and len of json_result: {type(json_result)}/{len(json_result)}")
    try:
        persona_data = json_result[0]
        persona = Person(**persona_data)
        print("Valid JSON object:", persona)
    except ValueError as e:
        print(e)

if __name__ == '__main__':
    loop = asyncio.get_event_loop()
    loop.run_until_complete(main())

type and len of result_raw: <class 'list'>/1
type and len of json_result: <class 'list'>/3
Valid JSON object: id=8 name='Donald' surname='Duck'

CREATE TABLE "public"."tmp" (
    "id" integer NOT NULL,
    "name" character varying NOT NULL,
    "surname" character varying NOT NULL,
    CONSTRAINT "tmp_pkey" PRIMARY KEY ("id")
);

INSERT INTO "tmp" ("id", "name", "surname") VALUES
(8, 'Donald',   'Duck'),
(9, 'Daisy',    'Duck'),
(10,    'Scrooge',  'McDuck');

select * from tmp;
 id |  name   | surname 
----+---------+---------
  8 | Donald  | Duck
  9 | Daisy   | Duck
 10 | Scrooge | McDuck
(3 righe)

I expect a json valid string in result_raw

Answer 1

Based on the docs here, you can tell asyncpg to deserialise the JSONB automatically. You would need your fetch_data_from_db function to look like this:

async def fetch_data_from_db():
    """get data from DB."""
    conn = await asyncpg.connect(DSN)
    await conn.set_type_codec(
        'jsonb',
        encoder=json.dumps,
        decoder=json.loads,
        schema='pg_catalog'
    )
    result = await conn.fetch(QUERY)
    await conn.close()
    return result

With this change, the contents of result_raw[0]['jsonb_agg'] is a list of dicts rather than a JSON string.