I have successfully used PostgreSQL hstore and json using ArcGIS Query Layers, and am actually pretty much convinced there are few limitations, if any:
- It may be, or likely is, necessary to use a WITH type query (PostgreSQL: Documentation: 9.6: WITH Queries (Common Table Expressions) to "hide" some of the complexity of using hstore and json functions for ArcGIS. E.g. you cannot include the hstore or json column directly in a Query Layer SQL statement as output column for an ArcGIS attribute table, however, if you use WITH, the lower "nested" queries in the WITH can use hstore and json columns and functions without problems. The resulting CTE will hide this complexity though for ArcGIS, and allow you to access the resulting data columns in ArcGIS. I have had great success with this in ArcGIS, just make sure the outermost SELECT * FROM X type query in the WITH SQL statement does not include the hstore or json(b) columns, so ArcGIS is not confronted with them, as said, only use them in the "inner" SQL statements.
- If you are doing automation and geoprocessing using arcpy and Python, using the pyodbc (Home · mkleehammer/pyodbc Wiki · GitHub ) package is a really powerful combination with Query Layers and hstore / json.
Using pyodbc, you can create views and materialized views and index & analyze your tables and materialized views to optimize performance. I have done that with great success as well in ArcGIS. Unfortunately, pyodbc is not part of the default install of ArcGIS, but you can use the Python package manager in Pro to easily install them.
If using pyodbc, you will also need to install the PostgreSQL ODBC drivers for Windows, you can find them here, make sure you select the proper version for your PostgreSQL installation:
