How to update more than 1 properties of jsonb field

postgresql-13

how to update the age and the status at once,

I have a jsonb field type, I need to update 2 properties (age and status).
I am able to update only either age and status, using the command below, how to do update both 'age' and 'status' at once?

**Update test Set attributes = jsonb_set(attributes, array['age'],to_jsonb(32))**

Best Answer

The jsonb_set function accepts a jsonb value as its first argument and returns another jsonb value, which is the result of the modification applied. You can pass the resulting jsonb value to another jsonb_set call where you would apply the second change. This would result in nested jsonb_set calls, as a_horse_with_no_name suggested in the comments. The statement would look something like this:

UPDATE
  test
SET
  attributes = jsonb_set(
                 jsonb_set(attributes, array['age'], to_jsonb(32))
               , array['status']
               , to_jsonb('some new status'::text)
               )
;

Related Solutions

Postgresql – How to query JSONB field type

You need to unnest the array:

Select * 
from ws a
  cross join jsonb_array_elements_text(a.queues -> 'omni_queue_pid') as o(id)
Where o.id::int = 5

It's unclear what result you exactly want, but an EXISTS condition might be more efficient:

select * 
from ws a
where exists (select *
              from jsonb_array_elements_text(a.queues -> 'omni_queue_pid') as o(id)
              Where o.id::int = 5)

Or using a jsonpath operator:

select * 
from ws
where queues @? '$.omni_queue_pid[*] == 5'

PostgreSQL partitions consuming huge amounts of memory

Yes, more partitions mean more overhead in memory.

Refer below text taken from docs:

It is also important to consider the overhead of partitioning during query planning and execution. The query planner is generally able to handle partition hierarchies with up to a few hundred partitions. Planning times become longer and memory consumption becomes higher as more partitions are added. This is particularly true for the UPDATE and DELETE commands. Another reason to be concerned about having a large number of partitions is that the server's memory consumption may grow significantly over a period of time, especially if many sessions touch large numbers of partitions. That's because each partition requires its metadata to be loaded into the local memory of each session that touches it.

With data warehouse type workloads, it can make sense to use a larger number of partitions than with an OLTP type workload. Generally, in data warehouses, query planning time is less of a concern as the majority of processing time is spent during query execution. With either of these two types of workload, it is important to make the right decisions early, as re-partitioning large quantities of data can be painfully slow. Simulations of the intended workload are often beneficial for optimizing the partitioning strategy. Never assume that more partitions are better than fewer partitions and vice-versa.

^{Reference: 5.11. Table Partitioning - 5.11.6. Best Practices for Declarative Partitioning (PostgreSQL 14 | Documentation)}

Best Answer

Related Solutions

Postgresql – How to query JSONB field type

PostgreSQL partitions consuming huge amounts of memory

Related Question