Convert JSON Arrays to SQL Junction Tables (Many-to-Many)

Many-to-Many Relationships from JSON

When a JSON object contains an array of references (like tags, categories, or roles), this typically represents a many-to-many relationship that requires a junction table in SQL.

Example JSON

{
  "id": 1,
  "title": "Getting Started with PostgreSQL",
  "tags": ["database", "postgresql", "tutorial", "beginner"]
}

Generated SQL

CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL
);

CREATE TABLE tags (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE article_tags (
  article_id INTEGER NOT NULL,
  tag_id INTEGER NOT NULL,
  PRIMARY KEY (article_id, tag_id),
  CONSTRAINT fk_article_tags_article FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
  CONSTRAINT fk_article_tags_tag FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

CREATE INDEX idx_article_tags_tag_id ON article_tags (tag_id);

Why a Junction Table

In relational databases, you cannot store an array directly in a column (well, PostgreSQL has array types, but they lose referential integrity). A junction table:

1. Enforces referential integrity — Every tag reference must point to a valid tag.
2. Enables querying from both sides — "All tags for article X" and "All articles with tag Y".
3. Supports extra metadata — You can add columns like added_at or sort_order to the junction table.

Detection Logic

The converter creates a junction table when it finds:

1. An array of scalar values (strings, numbers) — Creates both a lookup table and a junction table.
2. An array of objects with an id — Creates a junction table referencing the existing entity.

Composite Primary Key

Junction tables use a composite primary key (article_id, tag_id) instead of a surrogate ID. This:

• Prevents duplicate relationships by definition.
• Provides a natural covering index for lookups by article_id.
• Saves storage by not adding an unnecessary auto-increment column.

Adding Metadata

For more complex relationships, add columns to the junction table:

CREATE TABLE course_enrollments (
  student_id INTEGER NOT NULL,
  course_id INTEGER NOT NULL,
  enrolled_at TIMESTAMP NOT NULL DEFAULT NOW(),
  grade CHAR(2) NULL,
  PRIMARY KEY (student_id, course_id)
);

Scalar Arrays Alternative

If you do not need referential integrity and your database supports it, PostgreSQL's array type is a simpler alternative:

tags TEXT[] NOT NULL DEFAULT '{}'

However, this sacrifices querying efficiency (no index on individual elements without GIN) and cannot enforce that a tag exists in a master list.