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:

No sure try array_agg("Name"->>(json_object_keys("Name"))) AS name — Mihai
– Mihai, Commented May 22, 2014 at 16:58
For array agg I get "set-valued function called in context that cannot accept a set". For string agg I get "Error: string_agg(text) does not exist " — user3512424
– user3512424, Commented May 22, 2014 at 17:03
Insert those values in a temp table and use array_agg on that. — Mihai
– Mihai, Commented May 22, 2014 at 17:04
Not entirely sure how to do that tbh, been trying to convert the thing to an array but run into the same problem, where each row is {bob} {william} etc — user3512424
– user3512424, Commented May 22, 2014 at 17:49

pozs · Accepted Answer · 2014-05-23 09:51:02Z

4

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

edited May 23, 2014 at 9:51

answered May 23, 2014 at 8:12

pozs

36.6k5 gold badges61 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3512424 Over a year ago

Thank you for the explanation. Aggregate functions don't work on this though. It makes the database complain that a 'set-valued function call is called in context that cannot accept a set'

pozs Over a year ago

@user3512424 You're wrong, aggregate function will work on this translated example. i.e. array_agg("Name"->>"key") AS names. Your query makes a perfect example to not use set-returning functions in the SELECT clause, unless you're know exactly what you're doing.

user3512424 Over a year ago

I don't think I am. See the results in : imgur.com/8S4JAqq Oh, one second, i udnerstand what you're saying now.. will try again

Collectives™ on Stack Overflow

Concatenating JSON results to single column postgresql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related