JSON/JSONB Fields for Metadata

It's pretty clear that they are using JSON fields to store metadata. Why is that the right move and what are some example queries?

Using JSONB for metadata is often the right choice in migration-heavy systems because metadata tends to be:

• Heterogeneous: different collections, institutions, or source systems have different fields.
• Evolving: mapping rules and fields change during a long migration program.
• Nested: creators, subjects, rights, technical metadata, and source provenance naturally have arrays and objects.
• Sparse: making a relational column for every possible optional metadata field produces very wide, mostly-null tables.
• Source-faithful: retaining source-shaped metadata can help auditing, remapping, and troubleshooting.

For example, an asset or archival record might have stable relational fields:

CREATE TABLE records (
  id uuid PRIMARY KEY,
  organization_id uuid NOT NULL REFERENCES organizations(id),
  external_id text NOT NULL,
  title text,
  created_at timestamptz NOT NULL DEFAULT now(),
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  UNIQUE (organization_id, external_id)
);

And metadata could contain institution-specific and nested descriptive data:

{
  "source_system": "LegacyDAM",
  "source_record_id": "IMG-001239",
  "creators": [
    { "name": "Ada Lovelace", "role": "photographer" }
  ],
  "subjects": ["computing", "portraits"],
  "rights": {
    "status": "copyrighted",
    "license": "All rights reserved"
  },
  "dimensions": {
    "width": 2400,
    "height": 1600,
    "unit": "px"
  },
  "migration": {
    "batch_id": "2026-04-01-org-42",
    "warnings": ["Missing original capture date"]
  }
}

Why not put everything in JSONB?

A senior-level answer should make clear that JSONB is a tool, not a substitute for schema design.

Keep values as normal columns when they are:

• required for nearly every record,
• used frequently in joins, sorting, filtering, or constraints,
• central to authorization or tenancy,
• needed for strong referential integrity,
• used in aggregates or reporting at scale.

For example, organization_id, external_id, record type, lifecycle status, and timestamps belong in relational columns. A controlled vocabulary such as subjects may be represented in JSONB during ingestion, but may eventually be normalized into tables if cross-record search, governance, synonym handling, and reporting become important.

A common migration architecture is:

1. Store the original source payload in raw_payload JSONB.
2. Store transformed but flexible metadata in metadata JSONB.
3. Promote stable, high-value fields into first-class relational columns.
4. Preserve provenance and validation warnings so loads are explainable and repeatable.

Useful PostgreSQL JSONB queries

Get a top-level scalar

SELECT id, metadata->>'source_system' AS source_system
FROM records;

• -> returns JSONB
• ->> returns text

SELECT id
FROM records
WHERE metadata->>'source_system' = 'LegacyDAM';

Access nested fields

SELECT
  id,
  metadata #>> '{rights,status}' AS rights_status,
  (metadata #>> '{dimensions,width}')::integer AS width
FROM records;

Find records that are copyrighted:

SELECT id, title
FROM records
WHERE metadata #>> '{rights,status}' = 'copyrighted';

Find images above a particular width:

SELECT id, title
FROM records
WHERE (metadata #>> '{dimensions,width}')::integer >= 2000;

Be cautious with casts: malformed source data can make a query fail. During migration validation, it is often safer to validate shape and type before assuming a value is numeric.

Check whether a key exists

SELECT id
FROM records
WHERE metadata ? 'rights';

Check for multiple top-level keys:

SELECT id
FROM records
WHERE metadata ?& ARRAY['rights', 'creators'];

Check whether at least one key exists:

SELECT id
FROM records
WHERE metadata ?| ARRAY['rights', 'license', 'copyright'];

Containment queries

@> asks whether JSONB contains a JSON fragment.

SELECT id, title
FROM records
WHERE metadata @> '{"rights": {"status": "public_domain"}}';

Find records with a specific subject:

SELECT id, title
FROM records
WHERE metadata @> '{"subjects": ["computing"]}';

This is especially useful for flexible metadata filters and works well with a GIN index.

Search an array of objects

Find records where at least one creator has the photographer role:

SELECT id, title
FROM records
WHERE metadata @> '{"creators": [{"role": "photographer"}]}';

For more complex conditions, expand the array:

SELECT DISTINCT r.id, r.title
FROM records r
CROSS JOIN LATERAL jsonb_array_elements(
  COALESCE(r.metadata->'creators', '[]'::jsonb)
) AS creator
WHERE creator->>'role' = 'photographer'
  AND creator->>'name' ILIKE '%lovelace%';

Find migration warnings or incomplete records

SELECT id, external_id, metadata->'migration'->'warnings' AS warnings
FROM records
WHERE jsonb_array_length(
  COALESCE(metadata #> '{migration,warnings}', '[]'::jsonb)
) > 0;

Records missing a required migration-era field:

SELECT id, external_id
FROM records
WHERE NOT (metadata ? 'source_record_id');

Or records where a nested field is blank:

SELECT id, external_id
FROM records
WHERE NULLIF(metadata #>> '{rights,status}', '') IS NULL;

Indexing JSONB

The standard starting point is a GIN index:

CREATE INDEX records_metadata_gin_idx
ON records
USING gin (metadata);

This helps containment queries such as:

WHERE metadata @> '{"source_system": "LegacyDAM"}'

If most queries use containment operators and index size matters, jsonb_path_ops can be a good option:

CREATE INDEX records_metadata_path_gin_idx
ON records
USING gin (metadata jsonb_path_ops);

But it supports a narrower set of operators, so the default GIN operator class is more flexible.

For a frequently queried scalar field, an expression index is often better:

CREATE INDEX records_rights_status_idx
ON records ((metadata #>> '{rights,status}'));

Then:

SELECT id
FROM records
WHERE metadata #>> '{rights,status}' = 'public_domain';

can use a targeted B-tree index.

A good rule: use GIN for exploratory or variable metadata queries; add expression indexes for known, high-volume access paths. If a JSON key becomes critical enough to drive core application behavior, consider promoting it to a real column.

Django examples

Django’s JSONField maps naturally to PostgreSQL jsonb.

class Record(models.Model):
    organization = models.ForeignKey("Organization", on_delete=models.CASCADE)
    external_id = models.CharField(max_length=255)
    title = models.TextField(blank=True)
    metadata = models.JSONField(default=dict)

Filter by a nested value:

Record.objects.filter(
    metadata__rights__status="public_domain"
)

Containment:

Record.objects.filter(
    metadata__contains={
        "subjects": ["computing"]
    }
)

Check that a top-level key exists:

Record.objects.filter(metadata__has_key="rights")

Add a GIN index:

from django.contrib.postgres.indexes import GinIndex

class Record(models.Model):
    # fields ...

    class Meta:
        indexes = [
            GinIndex(fields=["metadata"], name="record_metadata_gin_idx"),
        ]

For an interview, a concise framing is:

JSONB is valuable because migrated metadata is heterogeneous, nested, and likely to evolve. I would preserve source and provenance data in JSONB while keeping high-value, stable, constrained fields relational. I’d use GIN indexes for containment-style metadata searches and expression indexes or real columns for frequently queried fields. The key is balancing flexibility during migration with data quality, performance, and long-term maintainability.