How to convert PGobject to JSON string

23 次查看（过去 30 天）
Tim Darrah
Tim Darrah 2022-7-15
回答： Piyush Dubey 2023-6-27
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.

请先登录，再回答此问题。

回答（1 个）

Piyush Dubey
Piyush Dubey 2023-6-27
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.

类别

在 Help Center 和 File Exchange 中查找有关 JSON Format 的更多信息

标签

产品


版本

R2022a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by