PostgreSQL -How to use jsonb_array_length in where clause-CodePudding

We have a PostgreSQL table with jsonb column. Some of the nodes in the json can come in as array or object in the input. I am trying to write a query that will give me the array length, if the node is an array and array size is greater than 1

select
    count(*) as policycount, policynumber 
from
    policymaster
where 
        jsonb_typeof((payload-> 'node1'::text) -> 'node2'::text) = 'array'
-- and     jsonb_array_length((payload-> 'node1'::text) -> 'node2'::text) > 1
group by policynumber 
order by 1 desc

If I try adding

and jsonb_array_length((payload-> 'node1' -> 'node2') > 1

then I get

SQL Error [42601]: ERROR: syntax error at or near "group"
Position: 310

If I try

 and     jsonb_array_length((payload-> 'node1'::text) -> 'node2'::text) > 1

I get

SQL Error [22023]: ERROR: cannot get array length of a non-array

Since it is mix of object and array, having the check for array in

where 
    jsonb_typeof((payload-> 'node1'::text) -> 'node2'::text) = 'array'

also doesn't seem to help

How can I get only those records where node2 is an array and the size of that array is greater than 1?

Sub Question

When I executed the query by @jjanes as is

select
    count(*) as policycount, policynumber
from
    policymaster
where
        case when jsonb_typeof((payload-> 'node1'::text) -> 'node2'::text) = 'array'
        then   jsonb_array_length((payload-> 'node1'::text) -> 'node2'::text) > 1 
        else false end
group by policynumber
order by 1 desc     ;

The results was empty.

When I changed the input parameters to both of the function calls to below, then it gave the results I was expecting

select
    count(*) as policycount, policynumber
from
    policymaster
where
        case when jsonb_typeof(payload-> 'node1' -> 'node2') = 'array'
        then   jsonb_array_length(payload-> 'node1' -> 'node2') > 1 
        else false end
group by policynumber
order by 1 desc     ;

I just started working on PostgreSQL so do not have full understanding of the json / jsonb functions.

From What I have understood the ::text part on any jsonb object converts it from jsonb to text but not sure exactly how this part behaves

(payload-> 'node1'::text) -> 'node2'::text)

Can you explain that part. Maybe then it will help me understand why the query with ::text for both of these nodes doesn't work in the case statement, but works when used individually in a different way

Thank you

CodePudding user response：

The syntax error is just because your parentheses are unbalanced. If you fixed that, then you would get the other error you have been getting.

There is no guarantee that just using AND will short-circuit in the way you want. You can use CASE to force the 2nd not to execute unless the 1st gives the desired result, as described in the docs:

select
    count(*) as policycount, policynumber
from
    policymaster
where
        case when jsonb_typeof((payload-> 'node1'::text) -> 'node2'::text) = 'array'
        then   jsonb_array_length((payload-> 'node1'::text) -> 'node2'::text) > 1 
        else false end
group by policynumber
order by 1 desc     ;

The reason the CTE doesn't work in recent versions is that the planner just folds the CTE into the rest of the query and so comes up with the same plan. You can prevent that with with temp_cte as MATERIALIZED (

CodePudding user response：

Probably this can help

with temp_cte as (
 select * from policymaster 
 where  jsonb_typeof(payload-> 'node1' -> 'node2')='array'
)

select
    policynumber, count(*) as policycount 
from
    temp_cte
where 
   jsonb_array_length(payload-> 'node1' -> 'node2') > 1
group by policynumber 
order by 1 desc

You shouldn't cast the array as text inside jsonb_array_length function.