pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedTable column vales to JSON object keys?
4+ messages / 3 participants
[nested] [flat]
* Table column vales to JSON object keys?
@ 2021-02-11 20:06 Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Wells Oliver @ 2021-02-11 20:06 UTC (permalink / raw)
To: pgsql-admin
Trying to go from a table like:
g | e | angle | path
----+---+--------+----------------------
g1 | e1 | a1 | http://foo.com/a.mp4
g1 | e1 | a2 | http://foo.com/b.mp4
g1 | e1 | a3 | http://foo.com/c.mp4
To a table like this, with a column value per angle as a key.
g | e | obj
----+---+---------------------------------------------------------------------------------------------
g1 | e1 | {"a1": "http://foo.com/a.mp4";, "a2": "http://foo.com/b.mp4";,
"a3": "http://foo.com/c.mp4";}
Can't quite get there.
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Table column vales to JSON object keys?
@ 2021-02-11 20:13 David G. Johnston <david.g.johnston@gmail.com>
parent: Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2021-02-11 20:13 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
On Thursday, February 11, 2021, Wells Oliver <wells.oliver@gmail.com> wrote:
> Trying to go from a table like:
>
> g | e | angle | path
> ----+---+--------+----------------------
> g1 | e1 | a1 | http://foo.com/a.mp4
> g1 | e1 | a2 | http://foo.com/b.mp4
> g1 | e1 | a3 | http://foo.com/c.mp4
>
> To a table like this, with a column value per angle as a key.
>
> g | e | obj
> ----+---+---------------------------------------------------
> ------------------------------------------
> g1 | e1 | {"a1": "http://foo.com/a.mp4";, "a2": "http://foo.com/b.mp4";,
> "a3": "http://foo.com/c.mp4";}
>
> Can't quite get there.
>
>
>
How far can you get?
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Table column vales to JSON object keys?
@ 2021-02-11 20:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2021-02-11 20:15 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Wells Oliver <wells.oliver@gmail.com>; pgsql-admin
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thursday, February 11, 2021, Wells Oliver <wells.oliver@gmail.com> wrote:
>> Trying to go from a table like:
>>
>> g | e | angle | path
>> ----+---+--------+----------------------
>> g1 | e1 | a1 | http://foo.com/a.mp4
>> g1 | e1 | a2 | http://foo.com/b.mp4
>> g1 | e1 | a3 | http://foo.com/c.mp4
>>
>> To a table like this, with a column value per angle as a key.
>>
>> g | e | obj
>> ----+---+---------------------------------------------------
>> ------------------------------------------
>> g1 | e1 | {"a1": "http://foo.com/a.mp4";, "a2": "http://foo.com/b.mp4";,
>> "a3": "http://foo.com/c.mp4";}
>>
>> Can't quite get there.
> How far can you get?
I'm guessing something like
SELECT g, e, json_object_agg(angle, path) FROM ... GROUP BY g, e
would work, but haven't experimented.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Table column vales to JSON object keys?
@ 2021-02-11 21:04 Wells Oliver <wells.oliver@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Wells Oliver @ 2021-02-11 21:04 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin
Thanks! jsonb_object_agg() is indeed the solution.
On Thu, Feb 11, 2021 at 12:15 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > On Thursday, February 11, 2021, Wells Oliver <wells.oliver@gmail.com>
> wrote:
> >> Trying to go from a table like:
> >>
> >> g | e | angle | path
> >> ----+---+--------+----------------------
> >> g1 | e1 | a1 | http://foo.com/a.mp4
> >> g1 | e1 | a2 | http://foo.com/b.mp4
> >> g1 | e1 | a3 | http://foo.com/c.mp4
> >>
> >> To a table like this, with a column value per angle as a key.
> >>
> >> g | e | obj
> >> ----+---+---------------------------------------------------
> >> ------------------------------------------
> >> g1 | e1 | {"a1": "http://foo.com/a.mp4";, "a2": "http://foo.com/b.mp4";,
> >> "a3": "http://foo.com/c.mp4";}
> >>
> >> Can't quite get there.
>
> > How far can you get?
>
> I'm guessing something like
>
> SELECT g, e, json_object_agg(angle, path) FROM ... GROUP BY g, e
>
> would work, but haven't experimented.
>
> regards, tom lane
>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2021-02-11 21:04 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-02-11 20:06 Table column vales to JSON object keys? Wells Oliver <wells.oliver@gmail.com>
2021-02-11 20:13 ` David G. Johnston <david.g.johnston@gmail.com>
2021-02-11 20:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-02-11 21:04 ` Wells Oliver <wells.oliver@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox