Update postgres values with corresponding keys

Question

I want lower case the value in for specific keys:-

Table:

logs

id bigint , jsondata text

[
  {
    "loginfo": "somelog1",
    "id": "App1",
    "identifier":"IDENTIF12"

  },
{
    "loginfo": "somelog2",
    "id": "APP2",
    "identifier":"IDENTIF1"
  }
  ]

I need to lower only id and identifier.. Need acheive something as below

UPdATE SET json_agg(elems.id) = lowered_val...

SELECT
    id,
    lower(json_agg(elems.id))  as lowered_val                         
FROM logs,
    json_array_elements(jsondata::json) as elems  
GROUP BY id;

Answer 1

demo:db<>fiddle

This is not that simple. You need to expand and extract the complete JSON object and have to do this manually:

SELECT
    id,
    json_agg(new_object)                                                               -- 5
FROM (
    SELECT
        id,
        json_object_agg(                                                               -- 4                                
            attr.key,
            CASE                                                                       -- 3
                WHEN attr.key IN ('id', 'identifier') THEN LOWER(attr.value) 
                ELSE attr.value 
            END
        ) as new_object
    FROM mytable,
        json_array_elements(jsondata::json) WITH ORDINALITY as elems(value, index),    -- 1
        json_each_text(elems.value) as attr                                            -- 2
    GROUP BY id, elems.index                                                           -- 4
) s
GROUP BY id

1. Extract the arrays. WITH ORDINALITY adds an index to the array elements, to be able to group the original arrays afterwards.
2. Extract the elements of the array elements into a new record each. This creates two columns key and value.
3. If the key is your expected keys to be modified, make the related values lower case, leave all others
4. Rebuild the JSON objects
5. Reagggregate them into a new JSON array