Select an JSONB column

xavierlhomme

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

 

 

Hayden Welch

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.