Change JSON object values inside JSON array

Question

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?

klin · Accepted Answer · 2016-07-15 16:09:04Z

1

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)

edited Jul 15, 2016 at 16:09

answered Jul 15, 2016 at 15:28

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kamil Lelonek Over a year ago

What if I want to update all of the orders without specifying a particular id? Because when I remove WHERE clause, the inner SELECT picks all of the orders..

klin Over a year ago

I've edited the answer. The query updates all rows now.

Collectives™ on Stack Overflow

Change JSON object values inside JSON array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related