Processing record type from a jsonb_each query

Question

I store some data as JSON. I want to flatten the data using jsonb_each. The new column type is RECORD, but I don't how extract values from it.

SELECT T FROM (
  SELECT json_each_text(skills::json->'prizes') FROM users) AS T;

The output is

        jsonb_each            
---------------------------------
(compliance,2)
(incentives,3)
(compliance,0)
(legal,3)
(legal,2)
(international-contributions,3)

The type is RECORD.

pg_typeof 
-----------
record

I want to do an aggregate and GROUPBY, but I cannot figure out how to extract the first element(the string) and the second element (the value).

Answer 1

Here is a workaround I have found: JSON -> ROW -> JSON -> (string, integer) and then aggregate. But I am wondering if there is a shortcut and skip the ROW->JSON conversion.

SELECT U.key, AVG(U.value::int) FROM
  (SELECT   row_to_json(T)->'s'->>'key' AS key,
  row_to_json(T)->'s'->>'value' AS value
  FROM
    (SELECT jsonb_each(skills::jsonb->'prizes') AS s
    FROM users) AS T
    ) AS U
GROUP BY key;

Answer 2

Thanks a lot, @Arnaud, this seems like a not-very-common problem. I wasn't sure about json data structure after using row_to_json function, so I needed to validate that via:

SELECT  row_to_json(T) FROM 
  (SELECT jsonb_each((data->'app_metadata'->>'results')::jsonb) 
  FROM temp) AS T;

And once I got the keys structure, I could replicate your approach:

SELECT  row_to_json(T)->'jsonb_each'->>'key' as key, row_to_json(T)->'jsonb_each'->>'value' as value
   FROM (select jsonb_each((data->'app_metadata'->>'results')::jsonb) FROM temp) AS T