Concatenating JSON results to single column postgresql

Question

So, at the moment I have two columns in a table, one of which containing a JSON document, like so:

        CID:
        2
        Name:
        {"first" : "bob","1" : "william", "2" : "archebuster", "last" : "smith"}    

When I do a search on this column using:

  SELECT "CID", "Name"->>(json_object_keys("Name")) AS name FROM "Clients" WHERE 
  "Name"->>'first' LIKE 'bob' GROUP BY "CID";

I get:

  CID | name
--------------
  2   | bob
  2   | william
  2   | archebuster
  2   | smith

When really I want:

 CID | name
  2  | bob william archebuster smith

How would i go about doing this? I'm new to JSON in postgresql. I've tried string_agg and it wouldn't work, presumably because i'm working in a json column, despite the fact '->>' should type set the result to string

UPDATE:

Answer 1

First, you need to understand, if you include a set-returning function into the SELECT clause, you will create an implicit LATERAL CROSS JOIN.

Your query in reality looks like this:

SELECT "CID", "Name"->>"key" AS name
FROM "Clients"
CROSS JOIN LATERAL json_object_keys("Name") AS "foo"("key")
WHERE "Name"->>'first' LIKE 'bob'
GROUP BY "CID", "Name"->>"key"

If you really want to do that, you can apply an aggregate function here (possibly array_agg or string_agg).

SQLFiddle