Обсуждение: Table column vales to JSON object keys?
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
--
----+---+--------+----------------------
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"}
----+---+---------------------------------------------------------------------------------------------
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
wells.oliver@gmail.com
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.mp4To 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.
"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
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
wells.oliver@gmail.com