How to put the result of an sql query into an object?

Question

SQL

Hello.
The crux of the matter, I'm doing a sql query, here it is

SELECT  sensor_alerts.*, sensor_instances.description, 
          sensor_instances.lastupdated, groups.address, 
          groups.city, groups.country
      FROM sensor_alerts
      LEFT JOIN sensor_instances ON sensor_instances.id = sensor_alerts.sensorid 
      LEFT JOIN groups ON sensor_alerts.groupid = groups.parentid

I get back an object with string properties of this type

{
  description: 1321231231
  city: asdad
  addres: asgs
  country: sфывфы
}

how can i have the properties groups.address, groups.country, and groups.city be placed on the groups object?
what would happen like this

{
  description: 1321231231
  groups: {
    city: asdad
    addres: asgs
    country: sфывфы
  }
}

Answer 1

SELECT 
    sensor_alerts.*,
    sensor_instances.*,
    json_agg(groups.*) as groups
FROM sensor_alerts
      LEFT JOIN sensor_instances ON sensor_instances.id = sensor_alerts.sensorid 
      LEFT JOIN groups ON sensor_alerts.groupid = groups.parentid
GROUP BY sensor_alerts.id; --- скорее всего группировка не обязательна