Table column vales to JSON object keys?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Table column vales to JSON object keys?

Wells Oliver-2
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
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Table column vales to JSON object keys?

David G Johnston
On Thursday, February 11, 2021, Wells Oliver <[hidden email]> 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.
Reply | Threaded
Open this post in threaded view
|

Re: Table column vales to JSON object keys?

Tom Lane-2
"David G. Johnston" <[hidden email]> writes:

> On Thursday, February 11, 2021, Wells Oliver <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Table column vales to JSON object keys?

Wells Oliver-2
Thanks! jsonb_object_agg() is indeed the solution.

On Thu, Feb 11, 2021 at 12:15 PM Tom Lane <[hidden email]> wrote:
"David G. Johnston" <[hidden email]> writes:
> On Thursday, February 11, 2021, Wells Oliver <[hidden email]> 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
[hidden email]