PostgreSql query to sum all values in json array-CodePudding

I am writing this query for an use case on this table

______________________________________________________
|companyId |    detailsJson                          |
|----------| ----------------------------------------|
|12        |{"dataKeyOne":1.10, "dataKeyTwo":1.20}   |
|123       |{"dataKeyFour":2.12, "dataKeySeven":1.18}|
|134       |    {}                                   |
|342       |    {}                                   |
______________________________________________________

My output coming is after writing this query is :

select companyId, sum(value::float) as sum
      from tableA, jsonb_each_text(detailsJson)
group by companyId;

|companyId |    sum|
|----------|-------|
|12        |  2.30 |
|123       |  3.30 |

But I want if detailsJson is empty then I want these companyId with 0 sum as shown in table below :

|companyId |    sum|
|----------|-------|
|12        |  2.30 |
|123       |  3.30 |
|134       |  0.0  |
|342       |  0.0  |

How can I achieve this using PostgreSQL?

CodePudding user response：

You need to move the jsonb_each_text to the FROM clause so that you can use it in an outer join:

select t.companyid, 
       sum(d.value::float) as sum
from the_table t
   left join jsonb_each_text(t.detailsjson) as d(key, value) on true
group by t.companyid
order by t.companyid
;