SQL JSON — Applied Deep Merge, Diffs, Predicate Search

QUESTION 6

Deep merge — Rewrite one nested key without losing the rest

jsonb_set|| operatorMissing parent key

Background ▼

There are two ways to replace a single nested key. jsonb_set points at a position with a path and replaces the value there, while || overwrites keys at the same level. The overwrite performed by || covers that level only: if the value is an object, it is replaced whole rather than blended (a shallow merge).

SELECT jsonb_set(json_col, '{parent_key,child_key}', 'false'::jsonb, true),
       json_col || '{"top_key": 1}'::jsonb
FROM   table_name;  -- 4th argument true = add the key if it is missing

No parent means no insert either: the fourth argument only takes effect at the end of the path. On a row where parent_key itself is missing, jsonb_set changes nothing and returns the original value. And when any argument is NULL, the whole result is NULL.

Problem ▼

For prefs in the settings table, produce the result of setting email inside the notify object to false. Leave the other keys of notify and the other top-level keys as they are. Rows that have no notify, and rows whose prefs is NULL, must also end up with a notify whose email is false. Return user_id, prefs, sorted by user_id ascending.

Tables used

▸ settings

user_id	prefs (jsonb)
1	{"theme":"dark","notify":{"email":true,"push":true}}
2	{"notify":{"push":true}}
3	{"theme":"light"}
4	NULL

Expected Output ▼

user_id	prefs
1	{"theme": "dark", "notify": {"push": true, "email": false}}
2	{"notify": {"push": true, "email": false}}
3	{"theme": "light", "notify": {"email": false}}
4	{"notify": {"email": false}}

Model Answer ▼

SELECT
  user_id,
  COALESCE(prefs, '{}'::jsonb)                             -- a NULL column becomes an empty object
    || jsonb_build_object(
         'notify',
         COALESCE(prefs -> 'notify', '{}'::jsonb) || '{"email": false}'::jsonb
       ) AS prefs                                          -- build the subtree, then blend it into the top level
FROM     settings
ORDER BY user_id;

/*
  Execution order (logical evaluation order):
  1. FROM settings                          → Read 4 rows
  2. COALESCE(prefs -> 'notify', '{}')      → The notify subtree, or an empty object
  3. || '{"email": false}'                  → Overwrite only email inside the subtree
  4. jsonb_build_object('notify', ...)      → Wrap it in an object holding just notify
  5. COALESCE(prefs, '{}') || ...           → Shallow-merge it into the top level
  6. ORDER BY user_id                       → user_id ascending
  */

Explanation (table transitions & key points) ▼

SELECT
  user_id,
  COALESCE(prefs, '{}'::jsonb)
    || jsonb_build_object(
         'notify',
         COALESCE(prefs -> 'notify', '{}'::jsonb) || '{"email": false}'::jsonb
       ) AS prefs
FROM     settings
ORDER BY user_id;

LEGEND ▼

Rows read / loaded

① FROM settings ▼

FROM settingsRead all 4 rows of settings. Only user 1 and user 2 have notify; user 3 holds nothing but theme at the top level, and for user 4 the column itself is NULL.

1 / 6

user_id	prefs (jsonb)
1	{"theme": "dark", "notify": {"push": true, "email": true}}
2	{"notify": {"push": true}}
3	{"theme": "light"}
4	NULL

All 4 rows read

Key points

A deep merge is "extract → blend → wrap → blend": PostgreSQL has no recursive merge operator. Take out the subtree at the level you want to rewrite, overwrite inside it, wrap it back under its original key and blend that into the top level — those four moves are the general form of a deep merge. The deeper the nesting, the more wrapping steps you add.

NULL has to be killed in two places: a NULL column and a NULL extracted subtree are separate problems. Both || and jsonb_set return NULL if a single argument is NULL. Only with a COALESCE in both places does every row come out in the same shape.

Where jsonb_set fits: when the parent is known to exist, jsonb_set(prefs, '{notify,email}', 'false') is the shorter and more readable form. If a schema-like constraint (CHECK (prefs ? 'notify'), say) can guarantee the parent, choose that.

Anti-patterns

Applying jsonb_set as it comes: jsonb_set(prefs, '{notify,email}', 'false', true) returns user 3, which has no notify, completely unchanged, and for user 4, whose prefs is NULL, the result is NULL as well. Since no error is raised, there is nothing to tell you the update did not happen.

Overwriting a whole level with ||: prefs || '{"notify": {"email": false}}' replaces notify wholesale, so the push of user 1 and user 2 is erased. Forget that the merge performed by || is shallow and keys disappear quietly.

From the field: where default values live when settings are JSON

Holding user settings in a JSON column means new fields need no migration. The price is a large population of rows that "have not been set yet", and readers that handle NULL and missing keys on every access. There are three places to put the defaults. Fill in every key at write time (reads stay simple; changing a default means an UPDATE of every row). Layer them at read time with '{defaults}'::jsonb || prefs (a changed default takes effect immediately; you pay the cost on every read). Or fill them in the application layer (SQL cannot see the defaults at all). For a default that is itself part of the policy — notifications on or off, the kind of thing whose direction changes later — layering at read time causes the fewest accidents.

QUESTION 7

jsonb_each — Match two versions and list the changed keys

jsonb_eachFULL JOINIS DISTINCT FROM

Background ▼

An audit log or a change history wants "which key changed" as rows. Open the before and after documents downwards with jsonb_each and FULL JOIN them on the key, and changes, additions and removals all line up in one table. A key present on only one side leaves NULL on the other, so the comparison uses IS DISTINCT FROM rather than <>.

SELECT COALESCE(b.key, a.key) AS key, b.value, a.value
FROM      jsonb_each(before_col) AS b(key, value)
FULL JOIN jsonb_each(after_col)  AS a(key, value) ON a.key = b.key
WHERE  b.value IS DISTINCT FROM a.value;  -- two NULLs count as "the same"

With <> the additions and removals fall out: on a row whose key exists on one side only, one side of the comparison is NULL, so b.value <> a.value is UNKNOWN and does not pass WHERE. Only keys whose value changed survive, and additions and removals vanish silently.

Problem ▼

Each row of the record_versions table holds the pre-update before_doc and the post-update after_doc. For each record, produce one row per changed key. Keys whose value changed, keys that were added and keys that were removed are all in scope. The before_value of an added key and the after_value of a removed key are NULL. Return record_id, key, before_value, after_value, sorted by record_id ascending and key ascending.

Tables used

▸ record_versions

record_id	before_doc (jsonb)	after_doc (jsonb)
101	{"name":"A","price":100,"tag":"x"}	{"name":"A","price":120,"tag":"x"}
102	{"name":"B","price":50}	{"name":"B","price":50,"color":"red"}
103	{"name":"C","stock":3}	{"name":"C"}
104	{"name":"D"}	{"name":"D"}

Expected Output ▼

record_id	key	before_value	after_value
101	price	100	120
102	color	NULL	"red"
103	stock	3	NULL

Model Answer ▼

SELECT
  r.record_id,
  d.key,
  d.before_value,
  d.after_value
FROM   record_versions r
CROSS JOIN LATERAL (
  SELECT COALESCE(b.key, a.key) AS key,          -- pick up keys present on one side only
         b.value AS before_value,
         a.value AS after_value
  FROM      jsonb_each(r.before_doc) AS b(key, value)
  FULL JOIN jsonb_each(r.after_doc)  AS a(key, value) ON a.key = b.key
  WHERE  b.value IS DISTINCT FROM a.value       -- catch changes, additions and removals at once
) AS d
ORDER BY r.record_id, d.key;

/*
  Execution order (logical evaluation order):
  1. FROM record_versions              → Read 4 rows
  2. jsonb_each(before_doc)            → Open the before document into key-value rows
  3. jsonb_each(after_doc)             → Open the after document into key-value rows
  4. FULL JOIN ON a.key = b.key        → Match on the key, keeping one-sided rows
  5. WHERE IS DISTINCT FROM            → Drop the keys whose value is the same (3 rows)
  6. SELECT record_id, key, both values → Choose 4 columns
  7. ORDER BY record_id, key           → Record order, then key name
  */

Explanation (table transitions & key points) ▼

SELECT
  r.record_id,
  d.key,
  d.before_value,
  d.after_value
FROM   record_versions r
CROSS JOIN LATERAL (
  SELECT COALESCE(b.key, a.key) AS key,
         b.value AS before_value,
         a.value AS after_value
  FROM      jsonb_each(r.before_doc) AS b(key, value)
  FULL JOIN jsonb_each(r.after_doc)  AS a(key, value) ON a.key = b.key
  WHERE  b.value IS DISTINCT FROM a.value
) AS d
ORDER BY r.record_id, d.key;

LEGEND ▼

Rows read / loaded

① FROM record_versions ▼

FROM record_versionsRead all 4 rows of record_versions. The before and after documents sit side by side in one row, but in this shape they cannot be compared key by key.

1 / 6

record_id	before_doc (jsonb)	after_doc (jsonb)
101	{"name": "A", "price": 100, "tag": "x"}	{"name": "A", "price": 120, "tag": "x"}
102	{"name": "B", "price": 50}	{"name": "B", "price": 50, "color": "red"}
103	{"name": "C", "stock": 3}	{"name": "C"}
104	{"name": "D"}	{"name": "D"}

All 4 rows read

Key points

IS DISTINCT FROM compares NULL as a value: an ordinary comparison operator returns UNKNOWN when either side is NULL, while IS DISTINCT FROM answers true or false — "two NULLs are the same, one NULL alone is a difference". That property is what lets a single condition catch additions, removals and changes together.

Pick the key up from both sides with COALESCE: in the result of a FULL JOIN, an added key has a NULL b.key and a removed key has a NULL a.key. Select only one side and half of your rows come out with a NULL key.

Only top-level keys can be compared: jsonb_each opens one level, so a nested object is compared as a whole value. If you need to know which child key changed, descend with a recursive CTE or take the level you want to compare out in advance with #>.

Anti-patterns

Comparing the whole documents as text: before_doc::text <> after_doc::text only tells you whether something changed, never which key. If the column is the json type, a difference in whitespace or key order alone can be reported as a change.

Settling for LEFT JOIN: with a LEFT JOIN, a key that exists only in the after document never appears in the join result, so additions are structurally undetectable. No error is raised; you simply see one side of the diff.

From the field: what to keep in an audit log

Ways of keeping a change history divide broadly into snapshots (store the whole document every time) and diffs (store only the keys that changed). A snapshot keeps writes simple and restores the state at a point in time as it was, at the price of a volume proportional to the number of updates. A diff is light, but reaching the state at a point in time means accumulating from the initial value. What practice usually settles on is storing snapshots and deriving the diff at read time with a query like this one. The write path stays simple while a "who changed what" screen can be added later. Moving to a diff-storing design can wait until updates are frequent enough that snapshot volume becomes the problem.

QUESTION 8

Delete operators — Drop the keys and elements a public copy must not carry

- operator#- operatorPath notation

Background ▼

There are two operators for taking part of a JSON away. - drops a top-level key (given as text) or an array element (given as an integer index), and #- drops the single point named by a path array, so it reaches into the nesting. Both do nothing and return the original value if what they point at does not exist.

SELECT json_col - 'top_key'                 AS a,  -- drop a top-level key
       json_col #- '{parent_key,child_key}' AS b,  -- drop something deeper down
       json_col #- '{arr_key,0}'            AS c   -- drop the first element of an array
FROM   table_name;

Giving - text means different things to different targets: against an object it is "drop that key", but against an array it is "drop every element equal to that string". To drop by position, use an integer index or a #- path.

Problem ▼

From body in the documents table, build a public JSON with the items that must not leave the company removed. Three things are dropped: the top-level internal_memo key, the email key inside the owner object, and the first element of the tags array (an internal label). A document that has none of them is returned with that part unchanged. Return doc_id, public_body, sorted by doc_id ascending.

Tables used

▸ documents

doc_id	body (jsonb)
1	{"title":"T1","internal_memo":"internal only","owner":{"name":"A","email":"a@example.com"},"tags":["draft","sql","index"]}
2	{"title":"T2","owner":{"name":"B"}}
3	{"title":"T3","internal_memo":"m3","owner":{"name":"C","email":"c@example.com"},"tags":["draft","json"]}
4	{"title":"T4","owner":{"name":"D","email":"d@example.com"},"tags":[]}

Expected Output ▼

doc_id	public_body
1	{"tags": ["sql", "index"], "owner": {"name": "A"}, "title": "T1"}
2	{"owner": {"name": "B"}, "title": "T2"}
3	{"tags": ["json"], "owner": {"name": "C"}, "title": "T3"}
4	{"tags": [], "owner": {"name": "D"}, "title": "T4"}

Model Answer ▼

SELECT
  doc_id,
  ((body - 'internal_memo')      -- drop a top-level key
         #- '{owner,email}')     -- drop a key deeper down
         #- '{tags,0}' AS public_body  -- drop the first element of the array
FROM     documents
ORDER BY doc_id;

/*
  Execution order (logical evaluation order):
  1. FROM documents            → Read 4 rows
  2. body - 'internal_memo'    → Drop one top-level key
  3. #- '{owner,email}'        → Drop email underneath owner
  4. #- '{tags,0}'             → Drop the first element of the tags array
  5. SELECT doc_id, public_body → Choose 2 columns
  6. ORDER BY doc_id           → doc_id ascending
  */

Explanation (table transitions & key points) ▼

SELECT
  doc_id,
  ((body - 'internal_memo')
         #- '{owner,email}')
         #- '{tags,0}' AS public_body
FROM     documents
ORDER BY doc_id;

LEGEND ▼

Rows read / loaded

① FROM documents ▼

FROM documentsRead all 4 rows of documents. Which of the three targets a document carries differs from row to row: doc 2 has no internal_memo, no email and no tags at all. The tags of doc 4 is an empty array.

1 / 5

doc_id	top-level keys	owner contents	tags
1	title, internal_memo, owner, tags	{"name": "A", "email": "a@example.com"}	["draft", "sql", "index"]
2	title, owner	{"name": "B"}	(no key)
3	title, internal_memo, owner, tags	{"name": "C", "email": "c@example.com"}	["draft", "json"]
4	title, owner, tags	{"name": "D", "email": "d@example.com"}	[]

All 4 rows read

Key points

A miss is not an error: the delete operators are designed to do nothing when what they point at is absent. That lets you apply the same expression to JSON whose shape varies per document without first sorting the rows. The flip side is that a misspelled path also slips through in silence, so confirm from the result that the deletion you intended actually happened.

An integer at the end of a path is an array index: '{tags,0}' is a text array, but where it points at an array the 0 is read as an index. A negative value counts from the end, so '{tags,-1}' is the last element. An out-of-range index, again, does nothing.

Dropping several keys at once: when there are several top-level keys, you can pass a text array in one go with body - ARRAY['internal_memo','draft_note']. The path form #- takes one point per call, so deletions that cross levels stack operators.

Anti-patterns

Naming a nested key with dot notation: body - 'owner.email' looks for a top-level key literally named owner.email, so nothing is deleted. No error is raised, and the value that must not be published stays where it was. Nesting is always named with a path array.

Leaving the masking to each SELECT: writing the public shaping per query means one forgotten spot leaks the raw value down that path. Freezing the masked shape as a view and exposing only that view to public paths prevents the leak structurally.

From the field: deleting and not showing are different things

The deletion in this question only takes values out of the query output; the values in the table are untouched. Honouring a request to erase personal data means an UPDATE on the row itself, plus a procedure that accounts for the same value still living in old backups, the WAL, audit logs and replicas. The most effective measure available at design time is to separate the fields you might have to erase into their own table from the start. Then erasure is a single row deletion, and the readers break in no way beyond losing one join.

QUESTION 9

@@ predicate — Narrow JSON rows by a numeric range

@@ operatorJSONPathType mismatch

Background ▼

The containment operator @> can only decide "does it contain this value" and cannot express a numeric comparison. @@, which evaluates a JSONPath predicate, takes a condition containing >= or && exactly as written. Where @? reports whether any element matches a path expression, @@ reports the truth of the predicate itself.

SELECT * FROM table_name
WHERE  json_col @@ '$.num_key >= 1000 && $.text_key == "value"';

CREATE INDEX ON table_name USING gin (json_col jsonb_path_ops);
-- jsonb_path_ops serves @>, @? and @@

A type mismatch is neither true nor false: $.num_key >= 1000 is a comparison between JSON numbers. On a row that stores the value as the string "1000", the result is undefined and the row does not pass WHERE. A row missing the key altogether is false.

Problem ▼

From the events table, retrieve the events whose amount in props is 1000 or more and whose channel is web. Return event_id, props, sorted by event_id ascending.

Tables used

▸ events

event_id	props (jsonb)
1	{"channel":"web","amount":1500}
2	{"channel":"app","amount":2000}
3	{"channel":"web","amount":800}
4	{"channel":"web","amount":"1200"}
5	{"channel":"web"}
6	{"channel":"web","amount":1000}

Expected Output ▼

event_id	props
1	{"amount": 1500, "channel": "web"}
6	{"amount": 1000, "channel": "web"}

Model Answer ▼

SELECT
  event_id,
  props
FROM   events
WHERE  props @@ '$.amount >= 1000 && $.channel == "web"'  -- evaluate the predicate as it stands
ORDER BY event_id;

/*
  Execution order (logical evaluation order):
  1. FROM events                    → Read 6 rows
  2. WHERE props @@ '...'           → Keep only rows where the predicate is true, 2 rows
  3. SELECT event_id, props         → Choose 2 columns
  4. ORDER BY event_id              → event_id ascending
  */

Explanation (table transitions & key points) ▼

SELECT
  event_id,
  props
FROM   events
WHERE  props @@ '$.amount >= 1000 && $.channel == "web"'
ORDER BY event_id;

LEGEND ▼

Rows read / loaded

① FROM events ▼

FROM eventsRead all 6 rows of events. The shape of props is not consistent across rows: event 4 has amount as a string, and event 5 has no amount key at all.

1 / 6

event_id	props (jsonb)
1	{"amount": 1500, "channel": "web"}
2	{"amount": 2000, "channel": "app"}
3	{"amount": 800, "channel": "web"}
4	{"amount": "1200", "channel": "web"}
5	{"channel": "web"}
6	{"amount": 1000, "channel": "web"}

All 6 rows read

Key points

Predicates are continuous with SQL's three-valued logic: a JSONPath comparison also returns one of true, false and undefined. Since WHERE passes only what is true, undefined ends up with the same outcome as false — but flip it with NOT and it stays undefined. Go after "the rows that fail the condition" with NOT and the rows whose types do not line up appear in neither result.

Choosing between @@ and @?: @@ returns the truth of a predicate expression ($.amount >= 1000), while @? returns whether a path expression ($.items[*] ? (@.qty > 0)) matches anything. The same idea can be written either way, but mixing them up raises no syntax error — only the result changes, and it changes quietly. Give @? a predicate expression and it finds one item, the truth value itself, so even '{}'::jsonb @? '$.amount >= 1000' is true and rows that fail the condition pass. Give @@ a path expression that returns anything other than a boolean and the result is NULL, so rows that ought to match drop out.

Index with jsonb_path_ops: the default jsonb_ops can also serve a bare key-existence search (?) and its index is correspondingly large; jsonb_path_ops narrows to @>, @? and @@ and comes out smaller and faster. If all you do is search by value, choose the latter.

Anti-patterns

Extracting, casting and then comparing: (props ->> 'amount')::int >= 1000 reads well but no GIN index can serve it, so every row is evaluated. Worse, the moment a non-numeric value is mixed in, the cast fails at run time and one bad row takes the whole query down.

Absorbing type drift on the search side: for a column where numbers and strings coexist, the workaround of listing both spellings with OR grows more tangled with every new condition. Putting a constraint such as CHECK (jsonb_typeof(props -> 'amount') = 'number') on the write side and lining the values up at the entrance is cheaper.

From the field: decide these before indexing a JSON column

A GIN index is a powerful way to make the contents of a JSON column searchable, but every update rewrites the index and writes get heavier. Two things are worth settling before you create one. First, which key you search by — if it is one particular key, an expression index such as CREATE INDEX ON events (((props ->> 'channel'))) is smaller and more effective than a GIN over the whole column. Second, whether that key really belongs inside the JSON — a key that always exists and is always searched can be lifted out as an ordinary column with a generated column (GENERATED ALWAYS AS ((props ->> 'channel')) STORED). Types, constraints, indexes and statistics all start working on it, and the flexibility of holding the rest as JSON remains.

QUESTION 10

Array containment — Select the rows that carry every tag

@> operator?& operatorDuplicates and order

Background ▼

Applied to a JSON array, @> decides whether the left side contains every element of the right side. Because the decision is made on sets, neither the order nor the duplication of elements affects the result. For an array of nothing but strings, the same thing can be written with ?& (has all) and ?| (has any), and a GIN index serves both.

SELECT * FROM table_name
WHERE  json_col -> 'arr_key' @> '["a","b"]'::jsonb;
-- for an array of strings, ?& array['a','b'] makes the same decision

Containment against a scalar has a different shape: json_col @> '{"arr_key":"a"}' means "the value of arr_key equals the string a", so it matches no row that holds an array there. To look inside the array, write the right side as an array too.

Problem ▼

In the articles table, meta holds a tags array. Retrieve the articles that carry both sql and postgres. Return article_id, tags, sorted by article_id ascending. tags is the array inside meta, returned as it is.

Tables used

▸ articles

article_id	meta (jsonb)
1	{"title":"Index Design","tags":["sql","postgres","index"]}
2	{"title":"Intro to SQL","tags":["sql"]}
3	{"title":"Reading Query Plans","tags":["postgres","sql"]}
4	{"title":"A Tuning Case Study","tags":["sql","sql","postgres"]}
5	{"title":"Draft","tags":[]}
6	{"title":"Notes"}

Expected Output ▼

article_id	tags
1	["sql", "postgres", "index"]
3	["postgres", "sql"]
4	["sql", "sql", "postgres"]

Model Answer ▼

SELECT
  article_id,
  meta -> 'tags' AS tags
FROM   articles
WHERE  meta -> 'tags' @> '["sql","postgres"]'::jsonb  -- contains both (order and duplicates ignored)
ORDER BY article_id;

/*
  Execution order (logical evaluation order):
  1. FROM articles                  → Read 6 rows
  2. meta -> 'tags'                 → Take the array out (NULL if the key is missing)
  3. WHERE ... @> '["sql","postgres"]' → Narrow to the 3 rows containing both
  4. SELECT article_id, tags        → Choose 2 columns
  5. ORDER BY article_id            → article_id ascending
  */

Explanation (table transitions & key points) ▼

SELECT
  article_id,
  meta -> 'tags' AS tags
FROM   articles
WHERE  meta -> 'tags' @> '["sql","postgres"]'::jsonb
ORDER BY article_id;

LEGEND ▼

Rows read / loaded

① FROM articles ▼

FROM articlesRead all 6 rows of articles. How the tags are held varies from row to row: article 4 has sql twice, article 5 has an empty array, and article 6 has no tags key at all.

1 / 5

article_id	meta (jsonb)
1	{"tags": ["sql", "postgres", "index"], "title": "Index Design"}
2	{"tags": ["sql"], "title": "Intro to SQL"}
3	{"tags": ["postgres", "sql"], "title": "Reading Query Plans"}
4	{"tags": ["sql", "sql", "postgres"], "title": "A Tuning Case Study"}
5	{"tags": [], "title": "Draft"}
6	{"title": "Notes"}

All 6 rows read

Key points

@> is set containment: it is true when every element on the right is present on the left, and extra elements do not matter. Order and duplication are ignored, so asking whether something contains ["sql","sql"] gives the same answer as asking about ["sql"]. When you want "exactly equal", use =.

?& and ?| read better: for an array of nothing but strings you can write meta -> 'tags' ?& array['sql','postgres'] (has all) and ?| (has any). The intent — AND search, OR search — appears directly in the operator, which makes them a readable choice for tag search.

To keep the index, do not move the left side: a GIN index is created on the column itself or on an expression such as (meta -> 'tags'). Write WHERE meta @> '{"tags":["sql","postgres"]}' against the whole column and the column's GIN is used; create an expression index and write the same expression and that one is used. The condition has to have the same shape as the index.

Anti-patterns

Expanding and counting with COUNT: opening the array with jsonb_array_elements_text, counting the matching tags and comparing against 2 counts 3 for a row like article 4 with its duplicate. You need a DISTINCT to flatten the duplicates, and no index helps either.

Searching the text with LIKE: meta::text LIKE '%sql%' matches any row whose text happens to contain sql anywhere — a tag such as postgresql or nosql, or a title that merely mentions it — while being case-sensitive, so a title spelled SQL slips through instead. A search that ignores the structure of the JSON always produces either over-matching or misses.

From the field: tags in a JSON array, or in a junction table?

The great advantage of holding tags in a JSON array is that reading and writing one article is one row. With a GIN index, an AND search with @> is fast enough, and adding a tag is just array manipulation in the application. The limit arrives when the tag becomes the subject — "list the tags by usage count", "rename a tag", "give a tag a description or a colour". Every one of those turns into expanding or updating every row, and there is nowhere to put a tag's own attributes. Read from the article side alone and an array is fine; start treating tags as something to be managed and you want the two tables tags and article_tags. The dividing line is the moment you want a tag to carry information beyond its name.