Changing a JSONB keys to uppercase

Question

I have a table with many fields, but the only important one here is meta which is a JSONB field (there is also a primary key, id, if needed). In this field, there is always a dict with key => value data, like this :

{
  "card": "gold",
  "country": "France",
  "Type of travel": "Business",
  "Company": "KLM"
}

I need to update this field to put every key in uppercase, like this :

{
  "CARD": "gold",
  "COUNTRY": "France",
  "TYPE OF TRAVEL": "Business",
  "COMPANY": "KLM"
}

The values, however, must keep their current cases - only the key are to be impacted.

I wrote a python script to do that, however it took multiple hours to update my testing set of 600k rows. The problem is that my production set is actually 2.5M rows and as long as the script is running I must stop the production, so I can't really have it running for days...

Is there any way to do this update in a full-sql way ? And would that be faster ?

I should add that there is no known list of possible keys in the field - the exact keys may (and do) vary from one rows to another, so I can't just say "here are the new keys", I need to retrieve them from them json object first.

Edit: The python script I tried to use is a Django management comment, so it use the ORM, but here it is :

print("Step 2: Verbatim...			Counting...", end='
')
verbatims = Verbatim.objects.all()
i = 0
total = len(verbatims)
to_save = []
for verbatim in verbatims:
  verbatim.meta = up_dict(verbatim.meta)
  to_save.append(verbatim)
  i += 1
  if i % 100 == 1:
    print(f"Step 2: Verbatim...			{i}/{total}", end='
')
  print("Step 2: Verbatim...			Saving...               ", end='
')
  Verbatim.objects.bulk_update(to_save, ['meta'], batch_size=10000)
  print("Step 2: Verbatim...			[OK]                    ")

It take around 5 secondes to go start the "counting" part, then around 2 seconds for the whole loop. Then the "saving" part was killed by the DB after 2 hours. Oh, and here is the up_dict function, if needed:

def up_dict(original):
    return {
        key.upper(): original[key] for key in original
    }

user_2275388 · Answer

Extract all elements with jsonb_each_text, use upper to change the keys and aggregate them again with jsonb_object_agg:

SELECT 
  jsonb_object_agg(upper(key),value)
FROM jsonb_each_text(
'{
  "card": "gold",
  "country": "France",
  "Type of travel": "Business",
  "Company": "KLM"
}'::jsonb);

                                   jsonb_object_agg                                    
---------------------------------------------------------------------------------------
 {"CARD": "gold", "COMPANY": "KLM", "COUNTRY": "France", "TYPE OF TRAVEL": "Business"}
(1 row)

Assuming that the jsonb document has an id of some sort, you can update your table like this:

WITH j (id,doc_upper) AS (
  SELECT id,jsonb_object_agg(upper(k),v) FROM t
  CROSS JOIN LATERAL jsonb_each_text(doc) j(k,v)
  GROUP BY id
)
UPDATE t SET doc = j.doc_upper
FROM j WHERE j.id = t.id;

Demo: db<>fiddle

Changing a JSONB keys to uppercase

1 Answers