I am trying to define the following GROUP BY query and need some help. I am using PostgreSQL DB table with a jsonb column
select
count(*),
policynumber
from
(
select
'My Query' as WhichQuery,
jsonb_path_query(AnchorNode, '$.HdrRoleData[*].PartyID')#>> '{}' as PartyID,
json_agg(json_build_object('Code', jsonb_path_query(AnchorNode, '$.Role[*].RoleData[*].Code')#>> '{}')) as Code,
policynumber,
lob as "LINE_OF_BUS"
from
(
select
PolicyNumber,
lob,
jsonb_path_query(payload,
'$.PolicyHdr.PolicyHdrPartyRole[*]') as AnchorNode
from
sch.fast_policymaster
) as my_select
group by
policynumber
order by
1 desc
) as countalias
When I run the above query, I get the below error
SQL Error [42803]: ERROR: column "my_select.AnchorNode" must appear in the GROUP BY clause or be used in an aggregate function
Position: 187
I also tried various PostgreSQL aggregate functions, but they also give an error similar to the one below
SQL Error [0A000]: ERROR: aggregate function calls cannot contain set-returning function calls
Hint: You might be able to move the set-returning function into a LATERAL FROM item.
Can I do a GROUP BY in this situation?
Answer to Comment Question
No. But this is the first part of the whole process I am tying to get working.
I am trying to compare the output from my inner query to output from another query from a different group that gets the same 4 attributes, where policynumber is key and for this query, each policy could have more than 1 partyids or codes, so my output shows all of that in separate rows.
But the count of rows retrieved from my query doesn't match the count of rows retrieved from the other query, there is a difference of 2. But since the number of rows retrieved are more than 200K for each query, I was trying to do the above query where I will get the rows for each policy for my query, and do a similar outer query for the other group's query and on top of that do an outer query that will compare the two results to find out the difference.
Corrected SQL
select
count(*),
policynumber
from
(
select
'My Query' as WhichQuery,
jsonb_path_query(AnchorNode, '$.HdrRoleData[*].PartyID')#>> '{}' as PartyID,
json_agg(json_build_object('Code', jsonb_path_query(AnchorNode, '$.Role[*].RoleData[*].Code')#>> '{}')) as Code,
policynumber,
lob as "LINE_OF_BUS"
from
(
select
PolicyNumber,
lob,
jsonb_path_query(payload,
'$.PolicyHdr.PolicyHdrPartyRole[*]') as AnchorNode
from
sch.fast_policymaster
) as my_select
) as countalias
group by policynumber
order by 1 desc
Thank you