python+asyncpg+PostgreSQL returns jsonb column as a string instead of an object

Question

I'm new to python and just came to a problem: i've set up a simple program to fetch some data from PostgreSQL database. They say that asyncpg library automatically converts JSONB data to Python objects (dicts/lists) by default. This doesn't happen in my case when I select data from a table. I've made synthetic example to demonstrate this:

import asyncio
import asyncpg
async def main():
 conn = await asyncpg.connect("postgres://user:pass@localhost/database")
 res = await conn.fetch("select '[1, 2, 3]'::jsonb as column;")
 for row in res:
 for key, value in row.items():
 print("'" + key + "'")
 print("'" + value + "'")
# Call main
if __name__ == "__main__":
 asyncio.run(main())

and the output is

'column'
'[1, 2, 3]'

Using debugger, I can see that value is a string as well. How can I fix that?

• furas: can you show link to information "asyncpg library automatically converts JSONB data to Python objects (dicts/lists) by default."
• furas: based on answer to python - PostgreSQL with asyncpg return a prefix string for a json_agg() select - Stack Overflow and Example: automatic JSON conversion it seems it may need await conn.set_type_codec(...) to convert it automatically

Answer 1

To automatically decode jsonb values to Python objects with asyncpg, you need to explicitly register a custom type codec:

import asyncio
import asyncpg
import json
async def main():
 conn = await asyncpg.connect("postgres://user:pass@localhost/database")
 await conn.set_type_codec(
 'jsonb',
 encoder=json.dumps,
 decoder=json.loads,
 schema='pg_catalog'
 )
 res = await conn.fetch("select '[1, 2, 3]'::jsonb as column;")
 for row in res:
 print(row['column'], type(row['column'])) # Output: [1, 2, 3] <class 'list'>
 await conn.close()
asyncio.run(main())

Answer 2

I can't test it because I don't PostgreSQL.

I didn't find information that it converts it automatically.

I see table with Type Conversion which suggests that json, jsonb is converted to str

But below is information how to set Custom Type Conversions which suggests to use conn.set_type_codec(...) and it shows example for json

import asyncio
import asyncpg
import json
async def main():
 conn = await asyncpg.connect()
 try:
 await conn.set_type_codec(
 'json',
 encoder=json.dumps,
 decoder=json.loads,
 schema='pg_catalog'
 )
 data = {'foo': 'bar', 'spam': 1}
 res = await conn.fetchval('SELECT 1ドル::json', data)
 finally:
 await conn.close()
asyncio.run(main())

I also found question python - PostgreSQL with asyncpg return a prefix string for a json_agg() select - Stack Overflow and first answer shows the same for jsonb

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