I need to transform json objects with arbitrary keys, and integer values like so

{"a":1, "sql":5} → {"a":{"f":1},"sql":{"f":5}}.

I can't figure out the correct postgres jsonb methods. I've set up this db fiddle to make it easy to interact.

Help is highly appreciated. Thanks in advance.

CodePudding user response：

You can do it with a combination of jsonb_each and jsonb_object_agg in a subquery:

SELECT (
  SELECT jsonb_object_agg(key, jsonb_build_object('f', value))
  FROM jsonb_each(f)
) AS transformed
FROM test

^{(updated fiddle)}

Without a subquery, you can also directly aggregate when fetching only a single row or when grouping by a row identifier:

SELECT id, jsonb_object_agg(key, jsonb_build_object('f', value)) AS transformed
FROM test, jsonb_each(f)
GROUP BY id

^{(adjusted fiddle)}

CodePudding user response：

SELECT jsonb_object_agg(j.key, jsonb_build_object('f', j.val))
  FROM ( VALUES
         ( 1, '{ "a": 1, "sql": 5 }'::jsonb )
       , ( 2, '{ "b": "test", "lqs": false }'::jsonb )
       ) t(id, jsonb_column)
 CROSS JOIN LATERAL jsonb_each(t.jsonb_column) j(key, val)
 GROUP BY t.id
;

Returns