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;

This would be sooo easy with a properly normalized data model — user330315
– user330315, Commented Mar 9, 2021 at 12:11

S-Man · Accepted Answer · 2021-03-09 12:21:41Z

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

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

Collectives™ on Stack Overflow

Update postgres values with corresponding keys

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related