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

Question

I have this table:

CREATE TABLE user_stats (username varchar, metadata_by_topic json);
INSERT INTO user_stats VALUES ('matt', '{"tech":["foo","bar"],"weather":"it is sunny"}');
INSERT INTO user_stats VALUES ('fred', '{"tech":{"stuff":"etc"},"sports":"bruins won"}');

The top-level keys in metadata_by_topic are always strings (e.g. "tech", "weather"), but the values under them are arbitrary json. I'd like a query that maps these top-level keys to their own column, and the json values to a different column, like so:

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

where username and topic are both of type VARCHAR and metadata is of type JSON. This:

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

only works if I add LIMIT 1 the inner select, but that's not what I want.

Joe Love · Accepted Answer · 2014-12-06 07:35:04Z

1

UPDATE: This is a better method

select username, key, metadata_by_topic->key
from
  (select username,  
      json_object_keys(
            (select t.metadata_by_topic from user_stats as t where t.username=us.username)
      ) AS KEY,
      us.metadata_by_topic
    from user_stats us
  ) x

edited Dec 6, 2014 at 7:35

answered Dec 5, 2014 at 19:09

Joe Love

6,1262 gold badges24 silver badges33 bronze badges

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

2 Comments

seanmac7577 Over a year ago

I don't think this degree of nesting will be performant enough for my application, but you answered the problem as asked, so thank you.

Joe Love Over a year ago

Have you tested the performance? I only ask because the postgres optimizer is an amazing thing sometimes.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related