squixy squixy - 4 months ago 13
JSON Question

Change JSON object values inside JSON array

I have a table of

orders
:

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


My
production_details
are represented as follows:

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"
},
...
]
}


And for each
production_detail
I'd like to change
timestamp
to just
date
.

I know I can select all
production_at
as:

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


However, how to update such JSON?

Answer

Unnest the array, edit elements and build the whole object afresh.

Example data:

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"
      }
   ]
}');

Query:

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
    );

Result:

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)
Comments