Postgres jsonb_object_agg returns only the last row-CodePudding

I want to transform a JSON table on a JSON list.

This code :

DO
$$
DECLARE
    varMyJson jsonb;
BEGIN

    varMyJson := '[{"Field1":"Value1"},{"Field2":"Value2"}]'::jsonb;
    RAISE NOTICE 'varMyJson : %', varMyJson;
    
    SELECT jsonb_object_agg(a.key, a.value)
    INTO varMyJson
    FROM
    (
        SELECT 'MyKey' as key, JsonString.value
        FROM jsonb_array_elements(varMyJson) JsonString
    ) a;
    RAISE NOTICE 'varMyJson : %', varMyJson;

END
$$

returns :

NOTICE:  varMyJson : [{"Field1": "Value1"}, {"Field2": "Value2"}]
NOTICE:  varMyJson : {"MyKey": {"Field2": "Value2"}}

But, I want this :

{"MyKey":{"Field1":"Value1"},"MyKey":{"Field2": "Value2"}}

I don't understand why it dosn't work.

CodePudding user response：

You cannot have a jsonb object with duplicate keys. Your json_object_agg function will work expectedly when your keys are unique.

You can get your desired results in a jsonb array:

with data as (
   select '[{"Field1":"Value1"},{"Field2":"Value2"}]'::jsonb as items
) 

select json_agg(v) from (
    select jsonb_build_object('myKey', jsonb_array_elements(items)) as v from data
) x

CodePudding user response：

SELECT json_object_agg('MyKey', JsonString.value)
  FROM json_array_elements('[{"Field1": "Value1"}, {"Field2": "Value2"}]' :: json) JsonString

result = { "MyKey" : {"Field1": "Value1"}, "MyKey" : {"Field2": "Value2"} }