postgresql query a json col with array of objects-CodePudding

in my table there is one cell that contains a json- array of objects, one of the properties from the object is an number(that i need to extract), the others are strings.

[{"a":"bla","b":"5k", "c":"foo"}, {"a":"bla","b":"9k", "c":"baz"}, {"a":"bla","b":"15k", "c":"foo"}]

^ a single json example.

select 
        jsonb_array_elements("jsons") ->> 'a' as a,
        jsonb_array_elements("jsons") ->> 'c' as c,

        regexp_replace(jsonb_array_elements("jsons")->> 'b', '\D','','g')::numeric as num
        
from x.y
where "some filter here"
and "condition 1"
and "condition 2"
and "condition 3"

this query gives me like 30 results,

the thing is, I cant find a way to sum the numbers. even a simple sum with no conditions returns an error.

I’m new to sql and don’t really know the depths of it, so im probably missing something over here.

what I would like to do is something like this

select 
        

 sum(
if(jsonb_array_elements("jsons") ->> 'a' = 'bla' and jsonb_array_elements("jsons") ->> 'c' = 'foo'
 then(regexp_replace(jsonb_array_elements("jsons")->> 'b', '\D','','g')::numeric)
   else(0)
  )) as sum
from x.y
where "some filter here"
and "condition 1"
and "condition 2"
and "condition 3"

I expect that in this case if all the jsons are the same as in the example above I will get a result of 600 (30 *(5 15))

CodePudding user response：

You can use a scalar subquery:

select t.*, 
       (select sum(regexp_replace(item ->> 'b', '\D', '')::numeric)
        from jsonb_array_elements(t.jsons) as x(item)
        where x.item ->> 'a' = 'bla'
          and x.item ->> 'c' = 'foo') as b_sum
from the_table t
;

CodePudding user response：

select
  sum(regexp_replace(e.value ->> 'b', '\D', '')::numeric) 
    filter (where e.value ->> 'a' = 'bla' and e.value ->> 'c' = 'foo')
from
  test t cross join jsonb_array_elements(t.jsons) e;