The customfieldvalue table is empty for Portfolio Team Customfield

[rahuldanwade]

I am writing a custom SQL Query for reporting and I am trying to pull data for Team custom field, but cannot find a value in the jiraissue table.

The customfieldvalue table is empty for customfield-20081 as well. 

Team Custom field ID: 20081 (this is not relevant though)

Team Data Table :AO_82B313_TEAM

select * from customfieldvalue where issue=(select id from jiraissue where issuenum=4684 and project=(select id from project where pkey='XYZ')) and customfield = 20081;

>>Empty Set

Is there a way to pull all issues for a specific project from the database where Portfolio (P4J) Team field value is not empty?

[Buu Le · Accepted solution]

Hi, the Portfolio custom field data is stored in entity_property table as JSON value.

[rahuldanwade]

thanks Buu Le.

Following query works

select json_value from entity_property join jiraissue on entity_property.ENTITY_ID = jiraissue.id where entity_name = "IssueProperty" and jiraissue.project = 12345;

[Tom Mason]

Thanks, this is just what I needed.