Переименование всех атрибутов в массиве объектов Json

Question

Например есть таблица в PG

CREATE TABLE public.json_objects(id serial primary key, objects text);

В ней хранятся массивы Json объектов

INSERT INTO public.json_objects (objects) VALUES ('[{"name":"Ivan"}]'), ('[{"name":"Petr"}, "surname":"Petrov"}]'), ('[{"form":"OOO"}, {"city":"Kizema"}]');

Как я могу заменить атрибут "name" на "first name" или "surname" на "second name" везде?

Я использую update с подзапросом select -. В этом случае произойдёт замена, но если атрибута не будет существовать в json объекте, тогда он добавится в json с null значением (а такого быть не должно)

WITH updated_table AS (SELECT id, jsonb_agg(new_field_json) as new_fields_json 
FROM (SELECT id, jsonb_array_elements(json_objects.objects::jsonb) - 'name' || jsonb_build_object('first name', jsonb_array_elements(json_objects.objects::jsonb) -> 'name') new_field_json FROM public.json_objects) r group by id) UPDATE public.json_objects SET objects = updated_table.new_fields_json FROM updated_table where json_objects.id = updated_table.id

Использование такого подхода не подойдёт, т.к. произойдёт замена и ключей и значений если они будут совпадать с шаблоном regexp:

update json_objects set objects = regexp_replace(objects, '(\"name"+)', '"first name"');

Answer 1

WITH RECURSIVE
cte1 (id, pattern, replacement) AS (
    SELECT 1, '"name":', '"first name":' UNION ALL
    SELECT 2, '"surname":', '"second name":' UNION ALL
    SELECT 3, '"firm":', '"firm name":' UNION ALL     -- non-existent property
    SELECT 4, '"city":', '"town":'
),
cte2 (id, objects, level) AS (
    SELECT id, objects, 1 FROM json_objects
    UNION ALL
    SELECT cte2.id, REPLACE(cte2.objects, cte1.pattern, cte1.replacement), cte2.level + 1
    FROM cte2
    JOIN cte1 ON cte2.level = cte1.id
),
cte3 (level) AS (
    SELECT MAX(level) FROM cte2
)
SELECT cte2.id, cte2.objects
FROM cte2
JOIN cte3 ON cte2.level = cte3.level
ORDER BY cte2.id

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=8d0aed2fcb567c691660e21a341dc3f3

Answer 2

Вариант с подзапросом для обновления, может кому то пригодится

UPDATE public.json_objects 
SET 
   objects = subquery.objects 
FROM 
(
WITH RECURSIVE 
cte1 (id, pattern, replacement) AS (
SELECT 1, '"name":', '"first name":'
),
cte2 (id, objects, level) AS (
SELECT id, objects::text, 1 FROM public.json_objects 
UNION ALL SELECT cte2.id, REPLACE(cte2.objects, cte1.pattern, cte1.replacement), 
cte2.level + 1 FROM cte2
JOIN cte1 ON cte2.level = cte1.id
),
cte3 (level) AS (
    SELECT MAX(level) FROM cte2
) 
SELECT cte2.id, cte2.objects
  FROM cte2
  JOIN cte3 ON cte2.level = cte3.level
  ORDER BY cte2.id
) AS subquery  
WHERE
   json_objects.id = subquery.id;

БЛОГ НА HUSL

Переименование всех атрибутов в массиве объектов Json

Ответы (2 шт):