Reduce rows into json key / value pair with postgres

If the table is called data, try this (jsonb_pretty is just for display purposes):

SELECT jsonb_pretty(
          jsonb_object_agg(key, value ORDER BY id)
       )
FROM data
WHERE userid = 1;

┌──────────────────────────────────────┐
│             jsonb_pretty             │
├──────────────────────────────────────┤
│ {                                   ↵│
│     "email": "[email protected]",↵│
│     "lastName": "reggi",            ↵│
│     "firstName": "thomas"           ↵│
│ }                                    │
└──────────────────────────────────────┘
(1 row)

This relies on the feature that jsonb does not keep duplicate keys.

It also relies on the fact that jsonb will always retain the last added key/value pair.

If you want to always have the latest value for a key, you could use a CTE and the RANK() window function:

SELECT * FROM p;
┌────┬───────────┬──────────────────────┬────────┬────────────────────────────┐
│ id │    key    │        value         │ userid │     modification_time      │
├────┼───────────┼──────────────────────┼────────┼────────────────────────────┤
│  1 │ email     │ [email protected]     │      1 │ 2016-10-05 12:53:32.936704 │
│  2 │ firstName │ thomas               │      1 │ 2016-10-05 12:53:32.936704 │
│  3 │ lastName  │ reggi                │      1 │ 2016-10-05 12:53:32.936704 │
│  4 │ email     │ [email protected] │      1 │ 2016-11-06 15:53:48.025775 │
└────┴───────────┴──────────────────────┴────────┴────────────────────────────┘
(4 rows)

WITH info_with_rank_for_user AS (
  SELECT userId,
         modification_time,
         value,
         key,
         RANK() OVER (PARTITION BY userId, key ORDER BY id DESC)
  FROM p
)
SELECT userId,
       json_object_agg(key, value),
       MAX(modification_time) AS last_settings_modification_time
FROM info_with_rank_for_user
WHERE rank = 1
GROUP BY userId
;
┌────────┬────────────────────────────────────────────────────────────────────────────────────┬─────────────────────────────────┐
│ userid │                                  json_object_agg                                   │ last_settings_modification_time │
├────────┼────────────────────────────────────────────────────────────────────────────────────┼─────────────────────────────────┤
│      1 │ { "email" : "[email protected]", "firstName" : "thomas", "lastName" : "reggi" } │ 2016-11-06 15:53:48.025775      │
└────────┴────────────────────────────────────────────────────────────────────────────────────┴─────────────────────────────────┘
(1 row)