Question 113972 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – transform jsonb with arbitrary keys

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.

Tags:

2

Answers


  1. 0

    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)

    Login or Signup to reply.
  2. 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}}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search