Building JSON from Postgres rows without column names

Question

I have a 3 colum PostgreSQL 9.4.4 tbl where k:TEXT, UNIQUE, v:INT, t:TEXT.

k      v      t
----------------
k1     3      x
k6     5      x
k11    3      y
k3     2      z
k4     2      y
k7     1      x
..     .      .

I'm trying to auto-generate 3 JSON arrays, seperately:

1. [{"k1":3},{"k6":5},{"k7":1}] (WHERE t=x)
2. [{"k11":3},{"k4":2}] (WHERE t=y)
3. [{"k3":2}] (WHERE t=z)

QUESTIONS:

First,

SELECT JSON_BUILD_OBJECT(k,v) FROM tbl

gives me individual json elements {"k1":3},{"k6":5}, etc. I can build full json array in my app (in Go), but it's hacky. How do I get the full [{"k1":3},{"k6":5},{"k7":1}]?

SELECT array_to_json(array_agg(row_to_json(tx))) FROM (SELECT k,v FROM tbl ) tx;

gives me column names, which I don't want: [{"k":"k1","v":3},{"k":"k6","v":5} etc. I need [{"k1":3},{"k6":5}, etc

Also,

SELECT JSON_BUILD_OBJECT(k,v) FROM tbl WHERE t=x;

errors so I can't filter on t. I don't want GROUP BY, I want to generate 3 different json arrays separately, on 3 different passes, depending on the value of WHERE t=. IOW, I don't want [{"y":[{"k11":3},{"k4":2}], etc

I am new to json ops in Postgres 9.4, I'd like to get the json properly done in the DB rather than mess with string manipulation outside of it. What am I overlooking here? Any help would be appreciated.

Clodoaldo Neto · Accepted Answer · 2015-11-06 11:42:56Z

2

with s (k,v,t) as ( values
    ('k1',3,'x'),
    ('k6',5,'x'),
    ('k11',3,'y'),
    ('k3',2,'z'),
    ('k4',2,'y'),
    ('k7',1,'x')
)
select t, array_to_json(array_agg(json_build_object(k,v)))
from s
where t = 'y'
group by t

edited Nov 6, 2015 at 11:42

answered Nov 5, 2015 at 12:27

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

4 Comments

parens Over a year ago

Thanks, @Clodoaldo. This does get me 3 rows of results x:[], y:[], z:[], as you showed above. My initial problem was to massage the SELECT statement with an additional filter so that I get a single result row per x (or y or z, but one at a time), as a json string: just x:[] or just y:[] or just z:[]. Any ideas?

Clodoaldo Neto Over a year ago

@parens You mean like using the where clause?

parens Over a year ago

Where would you put WHERE clause? ('FROM s WHERE t = y' errors.)

parens Over a year ago

Thanks, @Clodoaldo. Argh, my app code was composing the actual SELECT statement on-the-fly with a t value not as text but a variable. Fixed, works. Thanks.

Collectives™ on Stack Overflow

Building JSON from Postgres rows without column names

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related