postgresql - fan out each row into multiple rows per keys in a JSON column -

i have table:

create table user_stats (username varchar, metadata_by_topic json); insert user_stats values ('matt', '{"tech":["foo","bar"],"weather":"it sunny"}'); insert user_stats values ('fred', '{"tech":{"stuff":"etc"},"sports":"bruins won"}'); 

the top-level keys in metadata_by_topic strings (e.g. "tech", "weather"), values under them arbitrary json. i'd query maps these top-level keys own column, , json values different column, so:

username     | topic     | metadata ----------------------------------- matt         | tech      | ["foo","bar"] matt         | weather   | "it sunny" fred         | tech      | {"stuff":"etc"} fred         | sports    | "bruins won" 

where username , topic both of type varchar , metadata of type json. this:

select * json_each((select t.metadata_by_topic user_stats t)); 

only works if add limit 1 inner select, that's not want.

update: better method

select username, key, metadata_by_topic->key   (select username,         json_object_keys(             (select t.metadata_by_topic user_stats t t.username=us.username)       ) key,       us.metadata_by_topic     user_stats   ) x