[Resolved]: How to define the query for group by when using jsonb_query_path functions in the SELECT

Question

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

Answer 1

The SQL was formulated wrong, but the error message was sending me in all different directions when trying to resolve the issue. The corrected SQL should be as below

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