How to get data from a list Json with postgresql-CodePudding

I'm new to postgresql , I have no idea to get data

I try to get some data from JSON from PostgreSQL column:

SELECT http_status, total_access from my_table

Here is my postgresql data output:

 --- ------------------------------------------------------------------------------------------------------------------------------- --------------------- 
|   |http_status(json)                                                                                                              |total_access(integer)|
 --- ------------------------------------------------------------------------------------------------------------------------------- --------------------- 
| 1 |[{'key':'200','doc_count':1039440}, {'key':'302','doc_count':17172},{'key':'206','doc_count':11},{'key':'400','doc_count':2}]  |              1060184|
 --- ------------------------------------------------------------------------------------------------------------------------------- --------------------- 
| 2 |[{'key':'200','doc_count':843039}, {'key':'302','doc_count':33882},{'key':'206','doc_count':13},{'key':'400','doc_count':5}]   |               880526|
 --- ------------------------------------------------------------------------------------------------------------------------------- --------------------- 
| 3 |[{'key':'200','doc_count':707141}, {'key':'302','doc_count':11913},{'key':'206','doc_count':16},{'key':'400','doc_count':3}]   |               721234|
 ---------------------------------------------------------------------------------------------------------------------------------------------------------

How can I get key:2xx's doc_count sum / total

like -> (1039440 11 843039 13 707141 16)/(1060184 880526 721234)

result = 0.9728454092197281

CodePudding user response：

You can extract the values for specif keys through a JSON path query:

select sum(s.total_doc_count)::numeric / sum(t.total_access)
from the_table t
  cross join lateral (
    select sum(x.doc_count::int) as total_doc_count
    from jsonb_array_elements_text(jsonb_path_query_array(t.http_status, '$[*] ? (@.key like_regex "^2").doc_count')) as x(doc_count)
  ) s

This assumes that http_status is defined as jsonb (which it should be). If it's not you need to cast it http_status::jsonb - or better change the column type permanently.

Online example

CodePudding user response：

First partially 'normalize' (flatten) the data (my_table CTE is a mimic of the real table) and then calculate your result using a regular expression for filtering '2xx' keys only.

with my_table (http_status, total_access) as
(
 values
 ('[{"key":"200","doc_count":1039440},{"key":"302","doc_count":17172},{"key":"206","doc_count":11},{"key":"400","doc_count":2}]',1060184),
 ('[{"key":"200","doc_count": 843039},{"key":"302","doc_count":33882},{"key":"206","doc_count":13},{"key":"400","doc_count":5}]', 880526),
 ('[{"key":"200","doc_count": 707141},{"key":"302","doc_count":11913},{"key":"206","doc_count":16},{"key":"400","doc_count":3}]', 721234)
)
select sum((j ->> 'doc_count')::numeric)/(select sum(total_access) from my_table)
from my_table cross join lateral json_array_elements(http_status::json) as j
where j ->> 'key' ~ '^2\d{2}$';