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

0 votes

1 answer

499 views

                          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 : 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


                        

Asked by adbdkb (103 rep)

Oct 29, 2022, 11:29 AM
Last activity: Oct 30, 2022, 09:34 AM

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

Related Questions