Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

SELECT 
	jsonb_pretty(jsonb_agg(user_id || skills))
FROM (
	SELECT
		json_build_object('user_id', user_id)::jsonb as user_id,
		json_object_agg(skill_title, skills -> skill_title -> 'rating')::jsonb as skills
	FROM (
		SELECT 
			user_id,
			json_object_keys(skills) as skill_title,
			skills
		FROM (
			SELECT
				(datasets -> 'user_id')::text as user_id,
				datasets -> 'data' -> 'skills' as skills
			FROM(
				SELECT 
				  json_array_elements(json) as datasets
				FROM (
				  SELECT '[{"user_id": 123,"data": {"skills": {"skill_1": {"title": "skill_1","rating": 4,"description": "description text"},"skill_2": {"title": "skill_2","rating": 2,"description": "description text"},"skill_3": {"title": "skill_3","rating": 5,"description": "description text"}}}},{"user_id": 456,"data": {"skills": {"skill_1": {"title": "skill_1","rating": 1,"description": "description text"},"skill_2": {"title": "skill_2","rating": 3,"description": "description text"},"skill_3": {"title": "skill_3","rating": 4,"description": "description text"}, "skill_4": {"title": "skill_4","rating": 42,"description": "description text"}}}}]'::json
	)s)s)s)s

GROUP BY user_id
	ORDER BY user_id
)s

jsonb_pretty
[ { "skill_1": 4, "skill_2": 2, "skill_3": 5, "user_id": "123" }, { "skill_1": 1, "skill_2": 3, "skill_3": 4, "skill_4": 42, "user_id": "456" } ]

SELECT 1