How to convert PGobject to JSON string

I was previously using r2020a with the OBDC postgresql connection on this project that involved reading data from a database. Now only the JDBC and Native connections are available. I realized using the Native connection would break all of my select statements, so I am using JDBC. Now this is breaking all of the handling of json objects. My tables previously would display the actual JSON string in the "parameters" column, now it just displays 1x1 PGobject. My previous code to use the data was

params = my_table(i, 'parameters').parameters{1}
params = jsondecode(params)

I have this pattern repeated across numerous files in so many different places. Now I have to use

params = jsondecode(char(params.getValue)) 
% or
params = jsondecode(string(params))

My question is, how do I make this conversion within the select statement, or, immediately after the select statement and it apply to all rows? This way when I view the table, it is this

disp(table)

_____________________________________________________
| id   |   val1   |   val2   |    parameters
+------+----------+----------+-----------------------
| 1    |    1     |     1    |    {"param1": [1,2,3]}
+------+----------+----------+-----------------------
| 2    |    2     |     2    |    {"param2": [4,5,6]}
...

instead of

disp(table)

_____________________________________________________
| id   |   val1   |   val2   |    parameters
+------+----------+----------+-----------------------
| 1    |    1     |     1    |    {1x1 PGobject}
+------+----------+----------+-----------------------
| 2    |    2     |     2    |    {1x1 PGobject}
...
    

It seems like there should be some import options to set at the connection level, but I'm not sure. I looked into it and it appeared that the set options was for the native connection. My goal is to restart this work without having to make significant changes to my existing code due to being developed with r2020 and now I am useing r2022a.

Answer 1

[Piyush Dubey]

Hi Tim,

PGObjects can be casted to JSON objects in two ways:

1. Simply changing expected delivery type during the table is obtained by using get query. Somewhat like this:

 val sql = s"select row_to_json(t)::text as result from tablename;" 

2. Implicit conversion in scope of code

implicit val columnToJsValue:Column[JsValue] = 
    anorm.Column.nonNull[JsValue] { (value, meta) => val MetaDataItem(qualified, nullable, clazz)=
meta value match {          
    case json: org.postgresql.util.PGobject=>Right(Json.parse(json.getValue)) 
    case _ => Left(TypeDoesNotMatch(s"Cannot convert $value:         ${value.asInstanceOf[AnyRef].getClass} to Json for column $qualified")) 
          } 
       } 

In addition to jsondecode, a preprocessing step of getting data and removing an extra encapsulation of data in the object might help, implying inspecting the data inside the PGObject and putting it in place of the parameter column in the table.

Hope this helps.