Select an JSONB column

xavierlhomme — Tue, 01 Oct 2024 16:12:43 GMT

Hello

In a geoprocessing toolbox, I want to select an attribute containing a JSON, with the following code

sql = f"select jbdata from {table_jsonbdata}"

tmp = egdb_conn.execute(sql)

but I obtain this error

fail Exception : ArcSDESQLExecute: StreamBindOutputColumn Erreur ArcSDE -65 Invalid pointer argument to function

Exception : File "C:\Users\User01\Desktop\GestionDeProjet\Script-BDD\uploadBackpack.py", line 781, in main
tmp = egdb_conn.execute(sql)

I tried with JSONB : it failed

I tried with TEXT : it failed

I tried with VARCHAR : it failed

I tried with VARCHAR(2048) : it works

How should I store my json in the postgresql table in order to be able to selectit in a geoprocessing script ?

best regards

Re: Select an JSONB column

HaydenWelch — Thu, 17 Oct 2024 13:40:17 GMT

According to these docs, JSONB is not supported. VARCHAR(0) and VARCHAR both are read as CLOB type. I'm not sure why it was failing with VARCHAR though as CLOB should be able to store an arbitrary amount of text data.

If you do want to use JSONB, you could try using a different SQL module like sqlalchemy or psychopg2 to pull the data from the PostGIS database in the script, unless you need the whole schema to be fully compatible with Esri apis.

topic Re: Select an JSONB column in Python Questions

Select an JSONB column

Re: Select an JSONB column