Update json field with select in the same Postgresql table

Question

I have a table where I have to take data from a record and update it in a column from another record of the same table. These fields are like json.

I was able to get the data to be entered but I’m not able to do the update in the other record

select json_build_object(
        'one_signal',json_build_object(
            'oneSignalAppId', conf_json::jsonb->'oneSignalAppId',   
            'oneSignalAutoRegister', conf_json::jsonb->'oneSignalAutoRegister' 
        )       
    )::jsonb    
from config 
join loja
    on loja.loja_id = config.loja_id
    where conf_tipo = 6 and (conf_json::jsonb->'oneSignalAppId')::text != 'null' 

That one SQL return that:

The column that should receive this json has this data

{  
    "teste": {
        "ativo": true,
        "value": null
   },
     "resources": {
        "resources": {
            "urlLogoSite": "logo.png",
            "urlFaviconSite": null,
            "urlFooterPadraoEmail": null,
            "urlHeaderPadraoEmail": null
        }
    }
}

And after the update it should look like this:

{  
    "teste": {
        "ativo": true,
        "value": null
   },
     "resources": {
        "resources": {
            "urlLogoSite": "logo.png",
            "urlFaviconSite": null,
            "urlFooterPadraoEmail": null,
            "urlHeaderPadraoEmail": null
        }
    },
    "one_signal":{
          "oneSignalAppId": "6ac1c27c-7b17-4645-b5d2-098be724b52e", 
          "oneSignalAutoRegister": true
    }

}

The bank I’m using is the PostgreSQL 9.4. To make the update tried to use the json_object_agg but I couldn’t put together the current data with the data from select

Answer 1

I was able to solve it this way:

UPDATE config SET conf_json = (
    SELECT json_object_agg(key, value)::jsonb
        FROM (
            WITH to_merge AS (
                SELECT * FROM jsonb_each((
                    SELECT (conf_json)::jsonb
                        FROM config
                    JOIN loja
                        ON loja.loja_id = config.loja_id
                    WHERE conf_tipo = 19)
                )
            )
            SELECT * FROM jsonb_each((
                SELECT json_build_object(
                        'one_signal',json_build_object(
                        'appId', conf_json::jsonb->'oneSignalAppId',   
                        'autoRegister', conf_json::jsonb->'oneSignalAutoRegister' 
                    )       
                )::jsonb    
                FROM config 
                    JOIN loja
                        ON loja.loja_id = config.loja_id
                    WHERE conf_tipo = 6 AND (conf_json::jsonb->'oneSignalAppId')::text != 'null' )
            )
        WHERE key NOT IN (SELECT key FROM to_merge)
        UNION ALL
        SELECT * FROM to_merge
    ) t)
WHERE conf_tipo = 19