0

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

6
  • Your query makes no sense. You go to all the work to extract that data then don’t even return it in the outer query. Did you oversimplify it for the post?
    – dwhitemv
    Commented Oct 29, 2022 at 13:43
  • No. I am updating the question with the answer to this as the comment is not long enough for the explanation.
    – adbdkb
    Commented Oct 29, 2022 at 14:01
  • I’m even more confused now, so I will just say this: In general, if you are mixing aggregated and non-aggregated columns, perform the aggregation in a subquery.
    – dwhitemv
    Commented Oct 29, 2022 at 14:48
  • I will try to explain. My original query returns me 202888 rows, the other team's query returns 202890 rows - thus a difference of 2. There could be multiple rows with same policy number - so want to get output of policynumer and count of that from my query and same from another query. And then compare the two outputs to find out the policy numbers having mismatched count. So I can verify the original query. Hope this clarfies my intention - if you have a suggestion of how i can perform the aggregation in the subquery above, I would appreciate it
    – adbdkb
    Commented Oct 29, 2022 at 15:08
  • The issue is resolved. The group by in the above query was at wrong location, but the error I was getting was more confusing. This question is closed. I am updating the query to correct SQL
    – adbdkb
    Commented Oct 29, 2022 at 23:17

1 Answer 1

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.