isoos / postgresql-dart

Dart PostgreSQL driver: supports extended query format, binary protocol and statement reuse.
https://pub.dev/packages/postgres
BSD 3-Clause "New" or "Revised" License
128 stars 34 forks source link

Getting not decoded data when using `ARRAY_AGG` #75

Open buraktabn opened 1 year ago

buraktabn commented 1 year ago

I have the following SQL query using ARRAY_AGG.

select
  service.id,
  service.service,
  pro_service_category.*,
  ARRAY_AGG(pro_service_action.*) AS actions
FROM
  pro_service_action
  JOIN ...
 where sa.account_id  = @accountId and service.id =  @serviceId
group by 1,2,3

This query yields the following result in DBMS

[
  {
    ...,
    "actions": "{\"(11,16,Action 111,Action111 Description,true,1,0.0)\",\"(12,16,Action 112,Action 112 Description,false,2,50.0)\"}"
  }
]

However, when I query this in the dart, actions which is the result of the ARRAY_AGG returns a byte array.

final res = await connection.mappedResultsQuery(_getProServiceDetail, substitutionValues: {
  'accountId': accountId,
  'serviceId': request.id,
});

print(json.encode(res.first['']));
{"actions":[0,0,0,1,0,0,0,0,0,0,101,155,0,0,0,2,0,0,0,1,0,0,0,111,0,0,0,7,0,0,0,23,0,0,0,4,0,0,0,9,0,0,0,23,0,0,0,4,0,0,0,15,0,0,0,25,0,0,0,9,65,99,116,105,111,110,32,49,49,0,0,0,25,0,0,0,21,65,99,116,105,111,110,32,49,49,32,68,101,115,99,114,105,112,116,105,111,110,0,0,0,16,0,0,0,1,1,0,0,0,23,0,0,0,4,0,0,0,1,0,0,2,189,0,0,0,8,0,0,0,0,0,0,0,0,0,0,0,111,0,0,0,7,0,0,0,23,0,0,0,4,0,0,0,10,0,0,0,23,0,0,0,4,0,0,0,15,0,0,0,25,0,0,0,9,65,99,116,105,111,110,32,49,50,0,0,0,25,0,0,0,21,65,99,116,105,111,110,32,49,50,32,68,101,115,99,114,105,112,116,105,111,110,0,0,0,16,0,0,0,1,0,0,0,0,23,0,0,0,4,0,0,0,2,0,0,2,189,0,0,0,8,64,73,0,0,0,0,0,0]}

How can I decode this byte array to a human-readable format?

isoos commented 1 year ago

Unfortunately not all postgres types have been implemented (contributions are welcome!).

However, in such cases, you may cast/transform the result object to JSON or some other string format at the server and parse it back on the client.