What is a JSON Data Type?
According to PostgreSQL, "JSON data types are for storing JSON (JavaScript Object Notation) data, as specified in RFC 7159." To layman like most of us, JSON datatypes basically give database administrators (DBAs) flexibility.
For example, instead of creating a table column for "first_name", "last_name", and "job_description" you could instead create one JSON object underneath a column called "json" that encompassed all of these attributes. Here is an example of what JSON data type could look like:
JSON data types can be nested as seen above and retrieved with a SQL select statement like:
Notice how the select statement retrieves the 'first_name' of each author and presents it as if it was stored in table traditionally?
Does Yellowfin Support JSON Data Types?
If your database jdbc driver supports JSON data types than the answer is yes, however, accessing this data is not exactly user friendly. This is mostly because the data needs to be unpacked. However with a bit of SQL know how, JSON data types can be represented in Yellowfin reports.
Below is how the previously mentioned book table looks at the view level inside of Yellowfin:
In it's raw form, if you drag the 'packed' JSON column into a report using Yellowfin's Drag and Drop Report Builder you will receive this error:
To get around this, calculated fields need to be used at either the view or report level and must be built using free hand SQL as seen below:
View Level Calculated Field:
This is how the data will appear in the report:
You can accomplish this by creating a Report Calculated Field as well:
You can also accomplish this same result by creating the report using Freehand SQL:
Overall, this is a pretty niche feature, but one that is useful to know about as JSON data types grow in popularity!
For more information please visit the following URLs:
http://clarkdave.net/2013/06/what-can-you-do-with-postgresql-and-json/
http://www.postgresql.org/docs/9.4/static/datatype-json.html