Изменить значения объекта JSON внутри массива JSON

Question

У меня есть стол orders:

postgres=# \d orders
                                 Table "public.orders"
       Column       |            Type             |              Modifiers
--------------------+-----------------------------+-------------------------------------
 id                 | uuid                        | not null default uuid_generate_v4()
 production_details | jsonb                       |

мой production_details представлены следующим образом:

postgres=# SELECT production_details FROM orders;
{
   "data":[
      {
         "id":"1a24586c-c917-45d0-93d9-2d969fa6959d",
         "quantity":10,
         "production_at":"2016-04-17T00:00:00.000+00:00"
      },
      ...
   ]
}

И для каждого production_detail Я хотел бы изменить timestamp чтобы просто date,

Я знаю, что могу выбрать все production_at как:

SELECT (jsonb_array_elements(production_details->'data')->>'production_at') FROM orders;

Однако как обновить такой JSON?

Answer 1

Разверните массив, отредактируйте элементы и заново создайте весь объект.

Пример данных:

create table orders (id int, details jsonb);
insert into orders values (1, 
'{
   "data":[
      {
         "id":"1a24586c-c917-45d0-93d9-2d969fa6959d",
         "quantity":10,
         "production_at":"2016-04-17T00:00:00.000+00:00"
      },
      {
         "id":"1a24586c-c917-45d0-93d9-2d969fa6959x",
         "quantity":20,
         "production_at":"2016-04-18T00:00:00.000+00:00"
      }
   ]
}');

Запрос:

update orders o
set details = (
    select 
        json_build_object(
            'data', 
            jsonb_agg(
                jsonb_set(
                    e, 
                    '{production_at}', 
                    to_jsonb((e->>'production_at')::timestamp::date::text))
                )
            )
    from orders, jsonb_array_elements(details->'data') e
    where id = o.id
    );

Результат:

select id, jsonb_pretty(details) from orders;

 id |                       jsonb_pretty                        
----+-----------------------------------------------------------
  1 | {                                                        +
    |     "data": [                                            +
    |         {                                                +
    |             "id": "1a24586c-c917-45d0-93d9-2d969fa6959d",+
    |             "quantity": 10,                              +
    |             "production_at": "2016-04-17"                +
    |         },                                               +
    |         {                                                +
    |             "id": "1a24586c-c917-45d0-93d9-2d969fa6959x",+
    |             "quantity": 20,                              +
    |             "production_at": "2016-04-18"                +
    |         }                                                +
    |     ]                                                    +
    | }
(1 row)