Eliminate double quotes from a json field in a selective manner in PostgreSQL

I’m a new user of a PostgreSQL database with a json field which causes me some trouble. In fact, string values have been inserted into this json filed with double quote when they are supposed to be numerical values.

I have a field named “test_json” which is composed of for example these 2 rows:

{"test_name": "Full", "parameter1": "4.219", "parameter2": 4.4137}
{"test_name": "Full", "parameter1": "3.758", "parameter2": 4.159}

I would like the 2 lines above to be after correction in this form:

{"test_name": "Full", "parameter1": 4.219, "parameter2": 4.4137}
{"test_name": "Full", "parameter1": 3.758, "parameter2": 4.159}

I would like to correct these values without having to delete everything and I cannot correct it by hand because there are tens of thousands of “parameter1” values. Can you help me make this correction?

I would like to remove the double quotes for this parameter1 only and in case it is not numeric.

I find all the lines that have this non-numerical parameter1 with :

select *
    from t_test_result
   where jsonb_typeof (t_test_result.test_json -> ' parameter1') <> 'number'
     and t_test_result.test_json ? 'parameter1'

And I guess I have to use the update function afterwards, but I can’t manage it…

Thanks!

Franck

Answer

You can use jsonb_set() to turn change the datatype of the value of attribyte parameter1:

update t_test_result
set test_json = jsonb_set(
    test_json, 
    '{parameter1}', 
    to_jsonb((test_json ->> 'parameter1')::numeric)
)
where test_json ? 'parameter1'

Demo on DB Fiddle

Advertisement

Answer