How to query value from Postgresql jsonb colum-CodePudding

In a Postgres database I have a jsonb column that carries dimensions of items like the following one:

{
 "370730255061723346": {"volume": 3, "weight": 3200, "length": 8}, 
 "370730255061723353": {"volume": 2, "weight": 3600, "length": 9}
}

Since the first key is an ID, I´m struggeling with the addressing of the other information in the jsonb entry:

How do I retrieve the number of top level entries of the jsonb (here: 2)?
How can I fetch the value(s) of the key "length" - and ideally sum them up?

CodePudding user response：

You can use json functions like jsonb_each, jsonb_to_record, path extraction operators ... ie:

drop table if exists sample;

create temporary table sample
(
    id serial,
    dummy jsonb
);

insert into sample (dummy)
values ('{
  "370730255061723346": {
    "volume": 3,
    "weight": 3200,
    "length": 8
  },
  "370730255061723353": {
    "volume": 2,
    "weight": 3600,
    "length": 9
  }
}');

select *
from sample;

with myData as (
    select id, d.length, d.volume, d.weight
    from sample,
         lateral (select * from jsonb_each(sample.dummy)) t,
         lateral (select * from jsonb_to_record(t.value) as r(length int, volume int, weight int)) d
)
select sum(length)
from myData
where id = 1;

CodePudding user response：

Use the function jsonb_each() that expands the top-level JSON object into a set of key/value pairs.

select key, value
from my_table
cross join jsonb_each(jsonb_column)

Use the query as a source for the aggregates you need.

select 
    count(*) as number_of_entries, 
    sum((value->'length')::int) as sum_of_lengths
from (
    select key, value
    from my_table
    cross join jsonb_each(jsonb_column)
    ) s

Alternatively, in Postgres 12 you can use jsonb_path_query().

select 
    count(*) as number_of_entries, 
    sum((entry->'length')::int) as sum_of_lengths
from my_table
cross join jsonb_path_query(jsonb_column, '$.*') as entry

Test it in db<>fiddle.

Read about json functions in the docs.

CodePudding user response：

You need to turn the JSON into a series of rows, then aggregate back. To avoid having to group the entire query, I would do the aggregation in a derived table:

select t.other_column, d.*
from the_table t
   cross join lateral (
      select count(*) as num_elements, 
             sum((value ->> 'length')::int) as total_length
      from jsonb_each(t.the_column) as e(id, value)
   ) as d