Normalizing a JSON Export Into Relational Tables Safely

A JSON export from a content system, a CRM or a SaaS product usually comes as one big document per top-level thing, with everything it relates to nested inside. Posts carry their author, their tags and their comments. To query it as a database, you split it into tables, one per kind of thing, linked by keys. That is normalization, and it is mostly bookkeeping done with care: which things are entities, which fields describe them, which links are many-to-many, and what to do when the nested copies of the same entity disagree. This article loads a blog export of 5,000 posts into five tables in PostgreSQL and proves, with counts, that nothing was lost.

Find the entities and their relationships

Read one post and list the nouns:

{ "id": 3, "title": "Post 3",
  "author": { "id": 17, "name": "Author 17", "email": "[email protected]" },
  "tags": ["json", "perf"],
  "comments": [ { "id": 300, "by": "user22", "text": "Comment 0 on 3", "likes": 19 } ] }

The nouns are post, author, tag and comment. The relationships:

• A post has one author, and an author has many posts: many-to-one. The post stores author_id.

• A comment belongs to one post, a post has many comments: one-to-many. The comment stores post_id.

• A post has many tags and a tag is on many posts: many-to-many. It needs a link table.

The plan is five tables: authors, posts, tags, post_tags and comments. The load order follows the foreign keys: authors and tags first, then posts, then the link table and the comments.

Merge the duplicated copies on purpose

The author is embedded in every post, so each author is copied, in this export, about 125 times. Before taking one copy, check whether the copies agree:

SELECT count(*) AS posts,
       count(DISTINCT doc#>>'{author,id}') AS authors,
       count(DISTINCT (doc#>>'{author,id}', doc#>>'{author,name}', doc#>>'{author,email}')) AS id_name_email,
       count(*) FILTER (WHERE doc#>>'{author,twitter}' IS NOT NULL) AS with_twitter
FROM raw_posts;

 posts | authors | id_name_email | with_twitter
-------+---------+---------------+--------------
  5000 |      40 |            40 |          454

40 authors and 40 distinct (id, name, email) triples: the copies agree on those. But twitter is present on only 454 posts, and for a given author the list of values is {@a1, NULL}: some copies of the same author have the handle and some do not. An import that takes the first copy (or DISTINCT ON without care) would lose the handle for every author whose first post lacked it. The right merge takes the non-null value across copies:

INSERT INTO authors
SELECT (doc#>>'{author,id}')::int,
       min(doc#>>'{author,name}'),
       min(doc#>>'{author,email}'),
       max(doc#>>'{author,twitter}')
FROM raw_posts GROUP BY 1;

min and max ignore NULLs, so they pick the one real value when there is one. All 40 authors ended up with a twitter handle. When copies disagree on a non-null value, min/max silently chooses, so run the distinct count first and decide: latest wins, first wins, or stop and look. Any rule is fine if it is chosen and written down. This is the same question as in turning a deeply nested API response into three tables, where the nested address turned out to belong to a different entity.

Tags: a table and a junction table

Tags are shared values, and the names are the identity. Store each name once and link:

CREATE TABLE tags (id serial PRIMARY KEY, name text NOT NULL UNIQUE);
CREATE TABLE post_tags (
  post_id int REFERENCES posts(id),
  tag_id  int REFERENCES tags(id),
  PRIMARY KEY (post_id, tag_id)
);

INSERT INTO tags(name)
SELECT DISTINCT t FROM raw_posts p CROSS JOIN LATERAL jsonb_array_elements_text(p.doc->'tags') t ORDER BY 1;

INSERT INTO post_tags
SELECT (p.doc->>'id')::int, tg.id
FROM raw_posts p
CROSS JOIN LATERAL jsonb_array_elements_text(p.doc->'tags') t
JOIN tags tg ON tg.name = t;

The result: 10 tags and 9,964 links. The composite primary key (post_id, tag_id) does the dedupe job for free: a post that lists a tag twice would fail the load, and inserting a link a second time failed with duplicate key value violates unique constraint "post_tags_pkey", Key (post_id, tag_id)=(1, 1) already exists. If your source can repeat a tag in one post, add SELECT DISTINCT to the insert. Normalizing the case or whitespace of tag names (lower(trim(t))) belongs in the first insert, so SQL and sql do not become two tags.

A tag that is a many-to-many value deserves its own table even when you think you will never need more than the name. The first time somebody wants a description, a color or a count of posts per tag, there is a row to put it on.

Posts and comments

INSERT INTO posts
SELECT (doc->>'id')::int, (doc#>>'{author,id}')::int, doc->>'title',
       coalesce((doc->>'draft')::boolean, false)
FROM raw_posts;

INSERT INTO comments
SELECT (c->>'id')::int, (p.doc->>'id')::int, c->>'by', c->>'text', (c->>'likes')::int
FROM raw_posts p
CROSS JOIN LATERAL jsonb_array_elements(p.doc->'comments') c;

The draft key exists on 714 posts. In the table it is a boolean NOT NULL DEFAULT false: absence in the source means "not a draft", and the coalesce states that decision. That is a modeling choice; for a field where absence means "unknown", keep it nullable. The load took 0.02 s to 0.09 s per statement for the whole export. Counts: 40 authors, 5,000 posts, 10 tags, 9,964 post_tags, 9,183 comments.

Verify against the source

Compare with numbers taken directly from the documents:

SELECT (SELECT count(*) FROM raw_posts p, jsonb_array_elements_text(p.doc->'tags'))  AS src_tags,
       (SELECT count(*) FROM post_tags)                                              AS loaded_tags,
       (SELECT sum((c->>'likes')::int) FROM raw_posts p, jsonb_array_elements(p.doc->'comments') c) AS src_likes,
       (SELECT sum(likes) FROM comments)                                             AS loaded_likes;
-- 9964 | 9964 | 91540 | 91540

Tag links and likes match. Add the checks that foreign keys and constraints already make: nothing in post_tags without a post, nothing in comments without a post, no duplicate links. The tables carry their own proof, which a flat file cannot. Two facts from the load worth keeping in the notes: 838 of the 5,000 posts have no tags, so any query that joins post_tags with an inner join describes only 83% of the posts, and 1,612 posts have no comments.

The trap in the finished model

Normalized tables make some queries easy and one mistake easy to make. To count posts per tag, and the comments and likes on them, I joined tags to comments through the post:

SELECT t.name, count(*) AS posts, round(avg(c.likes), 2) AS avg_likes
FROM tags t JOIN post_tags pt ON pt.tag_id = t.id
JOIN comments c ON c.post_id = pt.post_id
GROUP BY 1 ORDER BY 2 DESC LIMIT 3;

mysql came back with posts = 1857. The tag is on 1,011 posts, and the 1,857 is the number of comment rows: the join to comments multiplied the rows, and count(*) counted the multiplied rows. The corrected query counts distinct posts and comments separately:

SELECT t.name, count(DISTINCT pt.post_id) AS posts, count(c.id) AS comments, sum(c.likes) AS likes
FROM tags t JOIN post_tags pt ON pt.tag_id = t.id
LEFT JOIN comments c ON c.post_id = pt.post_id
GROUP BY 1 ORDER BY 2 DESC LIMIT 3;

which gave postgres with 1,024 posts, 1,812 comments and 18,468 likes. The lesson is the one from the flat table, relocated: relationships that fan out (post to comments) multiply any count taken after a join. In a normalized model the fix is local: use count(DISTINCT key) for the parent, or aggregate the child table in a subquery first and join the result (why nested JSON does not fit a table explains why the shape causes it).

Size and effort

The five tables took 2,008 kB; the raw documents took 2,048 kB. With these small documents the keys saved by normalizing roughly paid for the indexes and the link table. The benefit here is not storage. It is that every fact has one home: an author's name is edited in one row, a tag is renamed in one row, and the database refuses a comment for a post that does not exist. For larger comment bodies the size picture improves further, as fewer bytes are repeated.

Keep the raw table until the model is trusted. It is the source for a reload when you discover that the likes should have been stored as a numeric or that a tag needs a different rule (storing raw API responses before you transform them).

When normalizing is more than you need

The comments are only ever displayed with their post. A jsonb column of comments avoids a table and a join, and costs you aggregation across comments.

The model is a one-time analysis. For a quick answer, DuckDB over the raw file is faster to write than five tables (flattening JSON with DuckDB struct and list functions).

The export format keeps changing. If every month brings new nested objects, a stable relational model is a moving target. Land the raw JSON, and normalize only the parts that have settled.

For background on the design rules, the PostgreSQL manual covers constraints and the set-returning JSON functions used above. Paste a post into the JSON formatter to read its structure first.