JSON columns in ArcSDE (Oracle)

Question

Are there any recommendations about using JSON columns in ArcSDE  (Oracle)?

In Oracle Database, JSON data is stored using the common SQL data types VARCHAR2, CLOB, and BLOB.

With the check constraint is json on this column you tell Oracle that you will store json.

I guess that you can create a featureclass in Oracle with python, and add this check constraint afterwards. Would that work?

I am aware that there are lots of issues with storing and querying json in relational databases as you can read here:

Why JSON fields shouldn’t be used in relational databases – Thomas Cerqueus

But are there any issues concerning ArcSDE which would make ArcSDE slower or reducing functionality?

bye, Nicole

NicoleSulzberger · Answer

Thanks a lot Joshua, these are clearly issues I have to think about properly.

We are having full control about the clients using the ArcGIS Server APIs, so invalid JSON input should be manageable.

We haven't planned using versioning or archiving yet, but in future this could be the case.

JoshuaBixby · Answer

When DBMS objects are participating in Esri's geodatabase model, I always discourage changing underlying DBMS functionality outside of Esri's tools. What you are asking about is technically possible, and likely won't abjectly break the geodatabase, but it raises lots of questions that should be considered before making such a change.  For example, how will the clients react to an Oracle error message (say a user doesn't input JSON) they are not expecting?  Also, if the feature class is versioned, do you know how to add the same constraint to all of the system tables (add/delete tables, etc...) that are involved in versioning that feature class?

At a minimum, you should have a very good understanding of the implementation details of the geodatabase model and the DBMS functionality if you are going to try something like you suggest.