How to read the propertyentry database table in Jira
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
The propertyentry database table stores a variety of data in Jira, from Issue Properties crucial to some features and apps (like Roadmaps Parent Link and Plans exclusion data) to system configs and app or plugin management.
This article aims at helping to read that table and join each property to the other tables where the actual property values are stored.
You may find the developer-oriented documentation useful, too:
Environment
Any version of Jira Software or Jira Service Management, both Data Center and Server.
Solution
Entity type and Property key distribution
You may learn the distribution of each property type and key through this query:
select
entity_name,
property_key,
count(id)
from
entity_property
/* where entity_name = 'IssueProperty' */ /* OPTIONAL FILTER */
group by
entity_name,
property_key
order by
3 desc, 2 asc;It should output something like:
ENTITY_NAME | PROPERTY_KEY | COUNT(ID)
---------------+---------------------------------------------+-----------
IssueProperty | sd.initial.field.set | 2919377
IssueProperty | jpo-issue-properties | 1680111
IssueProperty | jpo-exclude-from-plan | 199209
IssueProperty | request.channel.type | 70771
IssueProperty | request.public-activity.lastupdated | 60789
IssueProperty | feedback.token.key | 36503
IssueProperty | service-request-feedback-comment | 1761
IssueProperty | sd.kb.shared | 510
IssueProperty | codebarrel.automation.comment.action.6900 | 502Corresponding property values
To obtain the respective property values, we need to make left joins to 6 other possible tables based on the value in the propertyentry.propertytype:
| Value in propertytype | Table to join |
|---|---|
| 1, 2, 3 | propertynumber |
| 4 | propertydecimal |
| 5 | propertystring |
| 6 | propertytext |
| 7 | propertydate |
| 8, 9, 10, 11 | propertydata |
Here's an example query:
select
p.id
, p.entity_name
, p.entity_id
, p.property_key
, p.propertytype
, case
when p.propertytype in (1, 2, 3) then concat('', n.propertyvalue)
when p.propertytype = 4 then concat('', c.propertyvalue)
when p.propertytype = 5 then concat('', s.propertyvalue)
when p.propertytype = 6 then concat('', t.propertyvalue)
when p.propertytype = 7 then concat('', d.propertyvalue)
when p.propertytype in (8, 9, 10, 11) then concat('', x.propertyvalue)
else '?'
end as "propertyvalue"
from propertyentry p
left join propertynumber n
on p.propertytype in (1, 2, 3)
and n.id = p.id
left join propertydecimal c
on p.propertytype in (4)
and c.id = p.id
left join propertystring s
on p.propertytype in (5)
and s.id = p.id
left join propertytext t
on p.propertytype in (6)
and t.id = p.id
left join propertydate d
on p.propertytype in (7)
and d.id = p.id
left join propertydata x
on p.propertytype in (8, 9, 10, 11)
and x.id = p.id
where
lower(p.property_key) like lower('%...%') /* REPLACE SEARCH TERM HERE */
OR lower(p.entity_name) like lower('%...%') /* REPLACE SEARCH TERM HERE, TOO */
order by
p.property_key ASC;It should output something like:
id | entity_name | entity_id | property_key | propertytype | propertyvalue
-------+-----------------+-----------+---------------------------------------------------------------------+--------------+------------------------
10810 | INSIGHT-GENERAL | 1 | com.atlassian.assets.dedicated.node.progress.writes.to.db.frequency | 2 | 100
10808 | INSIGHT-GENERAL | 1 | com.atlassian.assets.index.object.load.attempts | 2 | 200
10809 | INSIGHT-GENERAL | 1 | com.atlassian.assets.index.object.load.interval | 2 | 100
10819 | INSIGHT-GENERAL | 1 | com.atlassian.assets.max.nodes.force.graph | 2 | 500
10807 | INSIGHT-GENERAL | 1 | com.atlassian.assets.replication.batch.delay | 3 | 400
10806 | INSIGHT-GENERAL | 1 | com.atlassian.assets.replication.batch.size | 2 | 1000
10817 | INSIGHT-GENERAL | 1 | com.atlassian.assets.replication.dlq.logger.interval | 3 | 300000