Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE t_test (
    id int,
    test_json jsonb
);

INSERT INTO t_test VALUES
(1, '{"test_name": "Full", "parameter1": "4.219", "parameter2": 4.4137}'),
(2, '{"test_name": "Full", "parameter1": "}", "parameter2": 4.159}'),
(3, '{"test_name": "Full", "parameter1": "u", "parameter2": 4.4137}'),
(4, '{"test_name": "Full", "parameter1": "3.758", "parameter2": 4.159}');

SELECT * FROM t_test;

CREATE TABLE



INSERT 0 4




id
test_json


1
{"test_name": "Full", "parameter1": "4.219", "parameter2": 4.4137}

2
{"test_name": "Full", "parameter1": "}", "parameter2": 4.159}

3
{"test_name": "Full", "parameter1": "u", "parameter2": 4.4137}

4
{"test_name": "Full", "parameter1": "3.758", "parameter2": 4.159}



SELECT 4


SELECT
    *
FROM
    t_test,
    jsonb_each_text(test_json) as objects


id
test_json
key
value


1
{"test_name": "Full", "parameter1": "4.219", "parameter2": 4.4137}
test_name
Full

1
{"test_name": "Full", "parameter1": "4.219", "parameter2": 4.4137}
parameter1
4.219

1
{"test_name": "Full", "parameter1": "4.219", "parameter2": 4.4137}
parameter2
4.4137

2
{"test_name": "Full", "parameter1": "}", "parameter2": 4.159}
test_name
Full

2
{"test_name": "Full", "parameter1": "}", "parameter2": 4.159}
parameter1
}

2
{"test_name": "Full", "parameter1": "}", "parameter2": 4.159}
parameter2
4.159

3
{"test_name": "Full", "parameter1": "u", "parameter2": 4.4137}
test_name
Full

3
{"test_name": "Full", "parameter1": "u", "parameter2": 4.4137}
parameter1
u

3
{"test_name": "Full", "parameter1": "u", "parameter2": 4.4137}
parameter2
4.4137

4
{"test_name": "Full", "parameter1": "3.758", "parameter2": 4.159}
test_name
Full

4
{"test_name": "Full", "parameter1": "3.758", "parameter2": 4.159}
parameter1
3.758

4
{"test_name": "Full", "parameter1": "3.758", "parameter2": 4.159}
parameter2
4.159



SELECT 12


SELECT
    *
FROM
    t_test,
    jsonb_each_text(test_json) as objects
WHERE value ~ '^[0-9\.]+$'


id
test_json
key
value


1
{"test_name": "Full", "parameter1": "4.219", "parameter2": 4.4137}
parameter1
4.219

1
{"test_name": "Full", "parameter1": "4.219", "parameter2": 4.4137}
parameter2
4.4137

2
{"test_name": "Full", "parameter1": "}", "parameter2": 4.159}
parameter2
4.159

3
{"test_name": "Full", "parameter1": "u", "parameter2": 4.4137}
parameter2
4.4137

4
{"test_name": "Full", "parameter1": "3.758", "parameter2": 4.159}
parameter1
3.758

4
{"test_name": "Full", "parameter1": "3.758", "parameter2": 4.159}
parameter2
4.159



SELECT 6


SELECT
    id,
    jsonb_build_object(key, value)
FROM
    t_test,
    jsonb_each_text(test_json) as objects
WHERE value ~ '^[0-9\.]+$'


id
jsonb_build_object


1
{"parameter1": "4.219"}

1
{"parameter2": "4.4137"}

2
{"parameter2": "4.159"}

3
{"parameter2": "4.4137"}

4
{"parameter1": "3.758"}

4
{"parameter2": "4.159"}



SELECT 6


SELECT
    id,
    jsonb_build_object(key, value::numeric)
FROM
    t_test,
    jsonb_each_text(test_json) as objects
WHERE value ~ '^[0-9\.]+$'


id
jsonb_build_object


1
{"parameter1": 4.219}

1
{"parameter2": 4.4137}

2
{"parameter2": 4.159}

3
{"parameter2": 4.4137}

4
{"parameter1": 3.758}

4
{"parameter2": 4.159}



SELECT 6


SELECT
    id,
    jsonb_object_agg(key, value::numeric)
FROM
    t_test,
    jsonb_each_text(test_json) as objects
WHERE value ~ '^[0-9\.]+$'
GROUP BY id, test_json


id
jsonb_object_agg


1
{"parameter1": 4.219, "parameter2": 4.4137}

3
{"parameter2": 4.4137}

2
{"parameter2": 4.159}

4
{"parameter1": 3.758, "parameter2": 4.159}



SELECT 4


SELECT
    id,
    jsonb_object_agg(key, value::numeric)
       ||  jsonb_build_object('test_name', test_json -> 'test_name') as test_json
FROM
    t_test,
    jsonb_each_text(test_json) as objects
WHERE value ~ '^[0-9\.]+$'
GROUP BY id, test_json


id
test_json


1
{"test_name": "Full", "parameter1": 4.219, "parameter2": 4.4137}

3
{"test_name": "Full", "parameter2": 4.4137}

2
{"test_name": "Full", "parameter2": 4.159}

4
{"test_name": "Full", "parameter1": 3.758, "parameter2": 4.159}



SELECT 4


UPDATE t_test t
SET test_json = s.test_json
FROM (
    SELECT
        id,
        jsonb_object_agg(key, value::numeric)
           ||  jsonb_build_object('test_name', test_json -> 'test_name') as test_json
    FROM
        t_test,
        jsonb_each_text(test_json) as objects
    WHERE value ~ '^[0-9\.]+$'
    GROUP BY id, test_json
) s
WHERE t.id = s.id;

SELECT * FROM t_test;

UPDATE 4




id
test_json


1
{"test_name": "Full", "parameter1": 4.219, "parameter2": 4.4137}

2
{"test_name": "Full", "parameter2": 4.159}

3
{"test_name": "Full", "parameter2": 4.4137}

4
{"test_name": "Full", "parameter1": 3.758, "parameter2": 4.159}



SELECT 4

id	test_json
1	{"test_name": "Full", "parameter1": "4.219", "parameter2": 4.4137}
2	{"test_name": "Full", "parameter1": "}", "parameter2": 4.159}
3	{"test_name": "Full", "parameter1": "u", "parameter2": 4.4137}
4	{"test_name": "Full", "parameter1": "3.758", "parameter2": 4.159}

id	test_json	key	value
1	{"test_name": "Full", "parameter1": "4.219", "parameter2": 4.4137}	test_name	Full
1	{"test_name": "Full", "parameter1": "4.219", "parameter2": 4.4137}	parameter1	4.219
1	{"test_name": "Full", "parameter1": "4.219", "parameter2": 4.4137}	parameter2	4.4137
2	{"test_name": "Full", "parameter1": "}", "parameter2": 4.159}	test_name	Full
2	{"test_name": "Full", "parameter1": "}", "parameter2": 4.159}	parameter1	}
2	{"test_name": "Full", "parameter1": "}", "parameter2": 4.159}	parameter2	4.159
3	{"test_name": "Full", "parameter1": "u", "parameter2": 4.4137}	test_name	Full
3	{"test_name": "Full", "parameter1": "u", "parameter2": 4.4137}	parameter1	u
3	{"test_name": "Full", "parameter1": "u", "parameter2": 4.4137}	parameter2	4.4137
4	{"test_name": "Full", "parameter1": "3.758", "parameter2": 4.159}	test_name	Full
4	{"test_name": "Full", "parameter1": "3.758", "parameter2": 4.159}	parameter1	3.758
4	{"test_name": "Full", "parameter1": "3.758", "parameter2": 4.159}	parameter2	4.159

id	jsonb_build_object
1	{"parameter1": "4.219"}
1	{"parameter2": "4.4137"}
2	{"parameter2": "4.159"}
3	{"parameter2": "4.4137"}
4	{"parameter1": "3.758"}
4	{"parameter2": "4.159"}

id	jsonb_build_object
1	{"parameter1": 4.219}
1	{"parameter2": 4.4137}
2	{"parameter2": 4.159}
3	{"parameter2": 4.4137}
4	{"parameter1": 3.758}
4	{"parameter2": 4.159}

id	jsonb_object_agg
1	{"parameter1": 4.219, "parameter2": 4.4137}
3	{"parameter2": 4.4137}
2	{"parameter2": 4.159}
4	{"parameter1": 3.758, "parameter2": 4.159}

id	test_json
1	{"test_name": "Full", "parameter1": 4.219, "parameter2": 4.4137}
3	{"test_name": "Full", "parameter2": 4.4137}
2	{"test_name": "Full", "parameter2": 4.159}
4	{"test_name": "Full", "parameter1": 3.758, "parameter2": 4.159}