PostgreSQL group_concat rows as json

Question

I have a query in mysql and want to do the same in PostgreSql.

Here's the query:

-- mysql
SELECT cat.id, cat.category,
CONCAT('[',
      GROUP_CONCAT(CONCAT('{"rate":"',mod.rate,'", "model_name":"', mod.modelName, '", "capacity":"',mod.capacity,'"}')), ']') vehicles 
      FROM categories cat 
      LEFT JOIN models mod 
      ON cat.id = mod.category_id 
      GROUP BY cat.id

This will get the output as:

id  |  category  |  vehicles
__________________________________________________________________________
1   |  2-wheeler |  [ { "rate": "400", "model_name": "Pulsar 12", ... }, {...} ]
2   |  4-wheeler |  [ { "rate": "800", "model_name": "Honda 12", ... }, {...} ]

I am trying to get the same with PostgreSql.

here's where I am stuck:

-- postgres
SELECT cat.id, cat.category,
CONCAT('[',
      array_to_string(CONCAT('{"rate":"',mod.rate,'", "model_name":"', mod.modelName, '", "capacity":"',mod.capacity,'"}')), ']') vehicles 
      FROM categories cat 
      LEFT JOIN models mod 
      ON cat.id = mod.category_id 
      GROUP BY cat.id

I don't know how to move forward with this.

Also, the tables:

models:

id  |  modelName   |  rate  |  category_id
_____________________________________________
1   |  Pulsar - 12 |  400   |  1
2   |  Honda       |  800   |  2

categores:

id  | category
__________________
1   | 2-wheeler
2   | 4-wheeler

Answer 1

You will have to use JSON_BUILD_OBJECT to build the individual JSON objects and then JSON_AGG to build an array of those objects:

SELECT cat.id, cat.category,
(SELECT json_agg(
  json_build_object(
    'rate',mod.rate,
    'model_name', mod.modelName, 
    'capacity',mod.capacity
  )) AS vehicles 
  FROM models AS mod WHERE category_id = cat.id
) AS models
FROM categories AS cat