transform jsonb with arbitrary keys

Question

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.

Bergi · Accepted Answer · 2022-10-31 18:06:11Z

2

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)}

edited Oct 31, 2022 at 18:06

answered Oct 31, 2022 at 17:42

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Miles Elam · Accepted Answer · 2022-10-31 18:11:24Z

0

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

jsonb_object_agg
{"a": {"f": 1}, "sql": {"f": 5}}
{"b": {"f": "test"}, "lqs": {"f": false}}

answered Oct 31, 2022 at 18:11

Miles Elam

1,80815 silver badges21 bronze badges

Collectives™ on Stack Overflow

transform jsonb with arbitrary keys

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related