I’m trying to style certain cells on a Perspective table based on whether they are approved or not. The way I used to do it would be to use a named query and then transform the dataset, adding styles and whatever else. However, I noticed that if you drag on a default Perspective Table component to a view, the first cell is styled a certain way (it’s orange).
Here is what data[0] looks like:
Based off of that, I got the idea to try returning a json object in a query that will add the style object to a certain cell from the query so that I don’t need to transform it.
However, the problem is that the data property treats the json object as a string instead of an object.
Here is what it appears as:
If I copy the string, convert json_title to an object, and then paste the string on the json_title, it works as expected.
The column render is set to auto as there is no option for object or anything of that sort. Any ideas?
This might be a limitation of the JDBC driver or our codebase; a lot of that code long predates databases with first-class support for JSON types.
As a workaround, you could add a transform that does something like system.util.decode() on the json_title column?
It doesn’t look like even the most current JDBC API has JSON types.
Would that need to happen for each row? If so, that defeats the purpose of trying to do it this way because I’m trying to avoid using a transform (for large datasets, the transform r e a l l y slows down the table component).
I’ll test this out right now and let you know what happens.
Installed the newest driver (8.0.26) and the issue still persists. The problem almost seems like its happening because JSON uses double-quotes and the value is being returned from the database as a double-quote enclosed string, like so:
"{\"style\": {\"color\": \"white\", \"backgroundColor\": \"red\"}}"
If I remove the back slashes, it works.
I’m assuming this isn’t possible then?
It seems that way, but I have no experiencing trying to use this JSON_OBJECT type, so I hesitate to say anything definitively.
I did use the system.util.jsonDecode function and I did get the styles to work. Usually what we do is return the dataset as json and then append style objects using a script (both row and column objects), so I was looking for a way to avoid doing that.
I looked into this issue a little more, and I might have been misunderstanding the JSON_OBJECT mysql function. It is supposed to return a string - just one that is json-formatted - and the receiving end would still need to do the decoding.
It would be nice to see this in a future version of Perspective (auto jsonDecode), if it would be possible.