json_agg() use and "<Record jsonb_agg=" string returned.

[langworr]

• asyncpg version: 0.28.0
• PostgreSQL version: 16.0
• Do you use a PostgreSQL SaaS?: no
• Python version: 3.11.5
• Platform: Linux
• Do you use pgbouncer?: no
• Did you install asyncpg with pip?: yes

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:password@127.0.0.1/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

[elprans]

fetch() always returns a list. You want to use fetchval instead when your query returns a single value.

[langworr]

Ok, I did some tests to understand better the process. I'll use fetchval().

Regards.