Jira Fields in SQL — what's available in your insights

This guide explains how Jira fields appear in the SQL database so you can write reports against them.

How your Jira data is organised

When the app syncs your Jira issues, it builds a relational database. The main table is work_items — one row per Jira issue. Other tables hold reusable lookup values (statuses, users, etc.) and child records (links, attachments, worklogs).

You write reports as SQL queries against these tables.

A few patterns to know:

Lookup table

A reference table (e.g. statuses, priorities, users) that lists each distinct value once with extra metadata. work_items stores the id of the looked-up row. To show the human-readable name, you join to the lookup table.

SELECT work_items.key, statuses.name AS status_name
FROM work_items
LEFT JOIN statuses ON statuses.id = work_items.status;

"Join" means: pair each row from one table with a matching row from another, using a column they share. LEFT JOIN keeps rows even if there's no match.

Scalar column

A single value lives directly in work_items — like summary (text), created (date), or story_points (number). No join needed.

SELECT key, summary, created FROM work_items;

Flattened scalars

Some Jira fields are small objects (e.g. Time tracking has original/remaining/spent values). Instead of one column with a JSON blob, we store several columns, one per useful property, named with the field id as a prefix: timetracking_original_estimate_seconds, timetracking_remaining_estimate_seconds, timetracking_time_spent_seconds.

SELECT key, timetracking_time_spent_seconds / 3600.0 AS hours_spent
FROM work_items;

Array column

Some fields hold a list of values (e.g. Labels, Components, Fix Versions). These live in work_items as a list column.

SELECT key FROM work_items WHERE array_contains(labels, 'urgent');

For lookup-style arrays (Components, Fix Versions) the list contains ids. To see the names, unnest the array and join the lookup table:

SELECT work_items.key, components.name
FROM work_items, UNNEST(components) AS t(component_id)
LEFT JOIN components ON components.id = t.component_id;

Child table

Some Jira fields contain too many sub-records to fit in a column. They get their own table with a work_item_id linking back to work_items. Examples: issue_links, attachments, worklogs.

SELECT users.displayName, SUM(time_spent_seconds) / 3600.0 AS hours
FROM worklogs
LEFT JOIN users ON users.id = worklogs.author_id
GROUP BY 1
ORDER BY 2 DESC;

work_items vs work_items_display

• work_items — the raw table. Lookup columns contain ids.
• work_items_display — same rows, but lookup columns are auto-joined to show names. Easier to read, slower to query.

When in doubt, start with work_items_display for ad-hoc exploration, switch to work_items for joins and aggregations.

Quick reference

Conventions used in this table:

• <field> — placeholder for the Jira field id. Built-in fields have stable ids like status, priority, resolution. Custom fields use customfield_NNNNN.
• The Columns on work_items / Related tables column always shows column name + SQL type on work_items. TEXT = string, REAL = floating-point number, INTEGER = whole number, DATE / DATETIME = date / timestamp, BOOLEAN = true/false, TEXT[] = list of strings.
• Lookup columns (TEXT FK) store the id of the row in the related table. To get human-readable names you join to that table (indicated by Table:) — see Related tables below the row.

Related tables

Quick summary of every table that may appear in the "Related table(s)" column:

A lookup table is joined via LEFT JOIN <table> ON <table>.id = work_items.<field>. A child table is joined via LEFT JOIN <table> ON <table>.work_item_id = work_items.id (it can have many rows per issue).

Field type details

Text — string

Stored as a plain TEXT column on work_items. Includes Summary, Description, Environment, Issue Color, Epic Name, free-text custom fields, etc.

SELECT key, summary FROM work_items WHERE summary ILIKE '%bug%';

Numbers — number

Stored as REAL (floating point). Includes Story Points, Time Spent, Time Estimate, Work Ratio, Σ Time Spent, etc.

SELECT SUM(timespent) / 3600.0 AS total_hours FROM work_items;

Dates and timestamps — date, datetime

date is just a day; datetime includes the time. Built-in datetime fields: created, updated, resolutiondate, lastViewed, statuscategorychangedate. Built-in date fields: duedate.

-- Issues created in the last 30 days
SELECT key, summary FROM work_items WHERE created > NOW() - INTERVAL 30 DAY;

Single-select options — option

Custom fields like radio buttons or single-select dropdowns. Stored as the option's display value.

SELECT customfield_10006 AS epic_status, COUNT(*) FROM work_items GROUP BY 1;

Multi-select / labels — array<string>, array<option>

Stored as a list column. The most common is labels.

-- Items tagged 'urgent'
SELECT key FROM work_items WHERE array_contains(labels, 'urgent');
 
-- How many of each label
SELECT l, COUNT(*) FROM work_items, UNNEST(labels) AS t(l) GROUP BY 1 ORDER BY 2 DESC;

Status — status

Lookup table statuses(id, name, description, iconUrl, statusCategoryId) and status_categories(id, name, key, colorName).

-- Items per status category
SELECT status_categories.name AS category, COUNT(*)
FROM work_items
LEFT JOIN statuses ON statuses.id = work_items.status
LEFT JOIN status_categories ON status_categories.id = statuses.statusCategoryId
GROUP BY 1;

Users — user, array<user>

Lookup table users(id, displayName, active, timeZone, accountType, emailAddress, avatarUrls). The id is the Atlassian account id. Use displayName for human-readable output.

Single-user fields: Assignee, Reporter, Creator. Multi-user fields (array): Approvers, Request Participants.

-- Issues per assignee
SELECT users.displayName, COUNT(*)
FROM work_items
LEFT JOIN users ON users.id = work_items.assignee
GROUP BY 1
ORDER BY 2 DESC;
 
-- Items where a specific user is an approver (multi-user field)
SELECT key FROM work_items WHERE array_contains(customfield_10505, 'AAA-account-id');

Issue type — issuetype

Lookup issuetypes(id, name, description, subtask, hierarchyLevel, avatarId, iconUrl).

SELECT issuetypes.name, COUNT(*) FROM work_items
LEFT JOIN issuetypes ON issuetypes.id = work_items.issuetype
GROUP BY 1;

Priority — priority

Lookup priorities(id, name, iconUrl).

SELECT priorities.name, COUNT(*) FROM work_items
LEFT JOIN priorities ON priorities.id = work_items.priority
GROUP BY 1
ORDER BY priorities.id;  -- priorities are typically id-ordered: Highest=1, ..., Lowest=5

Resolution — resolution

Lookup resolutions(id, name, description). work_items.resolution is NULL for unresolved issues.

-- Resolution rate by month
SELECT DATE_TRUNC('month', resolutiondate) AS month,
       resolutions.name AS resolution,
       COUNT(*) AS resolved
FROM work_items
JOIN resolutions ON resolutions.id = work_items.resolution
WHERE resolutiondate IS NOT NULL
GROUP BY 1, 2
ORDER BY 1;

Project — project

Lookup projects(id, key, name, description, projectTypeKey, style, simplified, isPrivate, leadId, components, issueTypes, versions, roles, properties). You usually want project_key directly on work_items (it's auto-derived from key).

SELECT projects.name, COUNT(*) FROM work_items
LEFT JOIN projects ON projects.id = work_items.project
GROUP BY 1;

Versions — version, array<version>

Lookup versions(id, name, archived, released, releaseDate). Fix Versions and Affects Versions are arrays.

-- Items per fix version (a single issue may appear multiple times)
SELECT versions.name AS fix_version, COUNT(*) AS items
FROM work_items, UNNEST(fixVersions) AS t(v)
LEFT JOIN versions ON versions.id = t.v
GROUP BY 1
ORDER BY 2 DESC;

Components — component, array<component>

Lookup components(id, name, description, project_id, leadId). Usually an array.

SELECT components.name, COUNT(*) AS items
FROM work_items, UNNEST(components) AS t(c)
LEFT JOIN components ON components.id = t.c
GROUP BY 1;

Groups — group, array<group>

Lookup groups(id, name). Used by group picker custom fields.

SELECT groups.name, COUNT(*)
FROM work_items, UNNEST(customfield_10511) AS t(g)
LEFT JOIN groups ON groups.id = t.g
GROUP BY 1;

Security level — securitylevel

Lookup security_levels(id, name, description).

SELECT security_levels.name, COUNT(*)
FROM work_items
LEFT JOIN security_levels ON security_levels.id = work_items.security
GROUP BY 1;

Issue links — issuelink, array<issuelinks>

The parent field is a single issuelink (stored as the parent's issue id directly on work_items.parent).

issuelinks (Linked Issues) and subtasks are arrays — they live in the issue_links child table with columns (id, source_issue_id, target_issue_id, link_type_id, link_type_name, link_direction).

-- How many "blocks" links does each issue have outgoing?
SELECT source_issue_id, COUNT(*)
FROM issue_links
WHERE link_type_name = 'Blocks' AND link_direction = 'outward'
GROUP BY 1;
 
-- Issues with parents joined to parent details
SELECT child.key AS child, parent.key AS parent, parent.summary
FROM work_items child
LEFT JOIN work_items parent ON parent.id = child.parent
WHERE child.parent IS NOT NULL;

Progress — progress, aggregateprogress

Three columns per field: _progress, _total, _percent. Built-in fields: progress (only this issue's work) and aggregateprogress (Σ Progress — issue + subtasks).

SELECT key, aggregateprogress_percent FROM work_items
WHERE aggregateprogress_percent < 50;

Time tracking — timetracking

Three columns: _original_estimate_seconds, _remaining_estimate_seconds, _time_spent_seconds.

SELECT key,
       timetracking_original_estimate_seconds / 3600.0 AS estimated_h,
       timetracking_time_spent_seconds / 3600.0 AS spent_h,
       (timetracking_time_spent_seconds - timetracking_original_estimate_seconds) / 3600.0 AS overrun_h
FROM work_items
WHERE timetracking_original_estimate_seconds IS NOT NULL;

Votes — votes

Two columns: votes_count, votes_has_voted.

SELECT key, votes_count FROM work_items ORDER BY votes_count DESC LIMIT 20;

Watchers — watches

Two columns: watches_count, watches_is_watching.

Attachments — attachment (array)

Child table attachments with (id, work_item_id, filename, mime_type, size, author_id, created). File content is not stored — only metadata.

-- Top 10 issues by total attachment size
SELECT work_items.key, SUM(attachments.size) AS bytes
FROM attachments
JOIN work_items ON work_items.id = attachments.work_item_id
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10;

Worklogs — worklog (array)

Child table worklogs with (id, work_item_id, author_id, started, time_spent_seconds, comment).

-- Hours logged per person this week
SELECT users.displayName, SUM(time_spent_seconds) / 3600.0 AS hours
FROM worklogs
JOIN users ON users.id = worklogs.author_id
WHERE started > NOW() - INTERVAL 7 DAY
GROUP BY 1
ORDER BY 2 DESC;

Comments

Not synced. Comment bodies aren't queryable. (Comments are unbounded and rarely useful in SQL reports.)

Team — team

Single TEXT column with the team name.

Sprint — custom gh-sprint

Lookup sprints(id, name, state, startDate, endDate, completeDate). The Sprint custom field on an issue holds a single sprint id (use the array form below if there are multiple).

-- Items in active sprints
SELECT work_items.key, sprints.name
FROM work_items
LEFT JOIN sprints ON sprints.id = work_items.customfield_10004
WHERE sprints.state = 'active';

Epic Link — custom gh-epic-link

Stored as the epic's issue key (e.g. PROJ-42) directly in the column.

-- Items grouped by epic
SELECT epic.summary AS epic_summary, COUNT(*) AS child_count
FROM work_items child
LEFT JOIN work_items epic ON epic.key = child.customfield_10005
GROUP BY 1;

Jira Service Management fields

SLA — sd-servicelevelagreement

Built-in JSM fields like Time to resolution and Time to first response. Eight columns per field:

-- Currently-breaching tickets
SELECT key, summary, customfield_10506_remaining_ms / 60000 AS minutes_remaining
FROM work_items
WHERE customfield_10506_breached = true
ORDER BY customfield_10506_remaining_ms;
 
-- Past SLA breach rate per ticket
SELECT key,
       customfield_10506_completed_breaches::REAL / NULLIF(customfield_10506_completed_count, 0) AS breach_rate
FROM work_items
WHERE customfield_10506_completed_count > 0;

Customer Request Type — sd-customerrequesttype

Two columns: _id, _name.

SELECT customfield_10023_name AS request_type, COUNT(*)
FROM work_items
WHERE customfield_10023_name IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC;

Organizations — array<sd-customerorganization>

Stored as an array of organization names.

SELECT org, COUNT(*) AS tickets
FROM work_items, UNNEST(customfield_10300) AS t(org)
GROUP BY 1
ORDER BY 2 DESC;

Satisfaction — sd-feedback

Two columns: _rating (1–5), _comment.

SELECT customfield_10025_rating, COUNT(*) FROM work_items
WHERE customfield_10025_rating IS NOT NULL
GROUP BY 1 ORDER BY 1;

Approvals — sd-approvals

Three columns: _count (total approvals on this issue), _latest_name, _latest_decision (approved / declined / pending).

SELECT customfield_10002_latest_decision, COUNT(*) FROM work_items
WHERE customfield_10002_count > 0
GROUP BY 1;

Sentiment — sd-sentiment

Two columns: _sentiment (positive/neutral/negative), _confidence (0..1).

Request language — sd-request-lang

Single TEXT column with the language code (e.g. en, de).

SELECT customfield_10508 AS lang, COUNT(*) FROM work_items
WHERE customfield_10508 IS NOT NULL
GROUP BY 1;

Field history (changelog)

Status, priority, assignee, and any other selected field changes are tracked in a separate history table with columns (work_item_id, project_key, date, field_id, value, seconds_in_state). This lets you compute time-in-status reports:

-- Average time issues spent in each status
SELECT statuses.name, AVG(seconds_in_state) / 86400.0 AS avg_days
FROM history
LEFT JOIN statuses ON statuses.id = history.value
WHERE field_id = 'status' AND seconds_in_state IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC;

seconds_in_state is NULL for the current value of a field. To include "time so far in current state" use COALESCE(seconds_in_state, EPOCH(NOW() - date)).

Finding field ids

If you don't know a custom field's id, check the fields table:

SELECT id, name, schema FROM fields WHERE name ILIKE '%story%';

Or look at the column autocomplete in the SQL editor — every synced field has a column or column group named after its id.