Spotfire: PostGres: JSONB column

Problem:

If you require Spotfire to read in a JSON or JSONB field from PostGreSQL, you will find that after creating a default Information Link it fails with an error such as:

InformationModelException at Spotfire.Dxp.Data:

Failed to execute query: Unsupported JDBC data type in query result: jsonb (HRESULT: 80131500)

Solution:

Edit the JSON or JSONB element and change the %1 to %1::varchar

Special:

If your JSONB is consistent, you can even pull in individual key values. Just create a new element (or column), for this data source.  Use the same JSONB original column as your source column.

If your JSON data is simple, you can use %1->>'key' (change key to the name of the JSON key) to get the value of the key.

If your JSON data is deeper in the file, you can use %1->'key1'->'key2'->>'key3' type effect (adjust the number of keys to suit).

If you want to return an integer, just change your Data type to Integer.

Give this element a new column name.  Then add it to your Information Link.

This box will be hidden from the public.
Source Case 285373