Aggregate columns / rows and convert them to valid JSON

Question

I'm still pretty new to PostgreSQL, and I'm having problems concatenating multiple rows and columns that I want to convert to valid JSON.

I have a table like this:

 uid |         name        | count 
-----+--------------------+-------
  15 | latergram          |     1
  15 | wales              |     1

I want to concatenate a column tag and read something like "name": count. Subsequently, I want to merge strings with the same uid into a single JSON object. The end result should be something like this:

 uid |          json_agg          
-----+----------------------------
  15 | ["latergram":1, "wales":1]

But best of all I can do this, which is invalid JSON:

 uid |          json_agg          
-----+----------------------------
  15 | ["latergram:1", "wales:1"]

This is the query I used:

SELECT foo.uid, json_agg(foo.tag) 
from (
    SELECT uid, (concat(tag || ':' || count)) as tag from test 
) as foo 
group by foo.uid

But isn't there a better and especially right way to achieve this through Postgres? Help would be greatly appreciated!

Answer 1

, :

with cte as(
   SELECT id, string_agg('"' || tag || '":' || count, ',') AS tag
   FROM Table1
   GROUP BY id
)
SELECT c.id, ('[' || c.tag || ']') FROM cte AS c

:

   uid |          json_agg          
    -----+----------------------------
    15 | ["latergram":1, "wales":1]

DEMO

Answer 2

to_json() yout name JSON.
CTE:

SELECT uid, '[' || string_agg(to_json(name) || ':' || ct, ',') || ']' AS tags
FROM   test
GROUP  BY 1;

ct count, SQL.

SQL-.