postgresql - Processing record type from a jsonb_each query -
i store data json. want flatten data using jsonb_each. new column type record, don't how extract values it.
select t ( select json_each_text(skills::json->'prizes') users) t;
the output is
jsonb_each --------------------------------- (compliance,2) (incentives,3) (compliance,0) (legal,3) (legal,2) (international-contributions,3)
the type record.
pg_typeof ----------- record
i want aggregate , groupby, cannot figure out how extract first element(the string) , second element (the value).
here workaround have found: json -> row -> json -> (string, integer) , aggregate. wondering if there shortcut , skip row->json conversion.
select u.key, avg(u.value::int) (select row_to_json(t)->'s'->>'key' key, row_to_json(t)->'s'->>'value' value (select jsonb_each(skills::jsonb->'prizes') s users) t ) u group key;
Comments
Post a Comment