Postgresql – Update dynamic jsonb array of objects

arrayjsonpostgresqlupdate

I am trying to update a jsonb key with a new value, but I cannot update all the keys at once.

My json structure is something like this:

[{
    "type": "button",
    "content": {
        "align": "leftAlign"
    }
}, {
    "type": "button",
    "content": {
        "align": "leftAlign"
    }
}, {
    "type": "button",
    "content": {
        "align": "leftAlign"

    }
}]

I want to update the align key with a new value, but right now my query does not update all the elements, it only updates one element

this is my query so far:

with align_position as (
    select 
    ('{' || index-1 || ',content,align}' )::text[] as path,
    id
    from section, jsonb_array_elements(entities) with ordinality arr(entity, index)
    where entity->'content'->>'align' = 'leftAlign'
)
update myTable set entities = jsonb_set(entities, align_position.path, '"left"', false) from align_position where section.id = align_position.id;

How can I make the query to update all the elements?

Any thoughts?

Best Answer

I managed to solve this using this query:

update
   myTable 
set
   entities = 
   (
      select
         to_jsonb(array_agg (new_values)) 
      from
         (
            select
(
               case
                  when
                     arr -> 'content' ->> 'align' = 'leftAlign' 
                  then
                     jsonb( COALESCE( jsonb_set(arr, '{content}', jsonb(arr ->> 'content') - 'align' || jsonb('{"align": "left"}'), true), '{}' ) ) 
                  when
                     arr -> 'content' ->> 'align' = 'rightAlign' 
                  then
                     jsonb( COALESCE( jsonb_set(arr, '{content}', jsonb(arr ->> 'content') - 'align' || jsonb('{"align": "right"}'), true), '{}' ) ) 
                  when
                     arr -> 'content' ->> 'align' = 'centerAlign' 
                  then
                     jsonb( COALESCE( jsonb_set(arr, '{content}', jsonb(arr ->> 'content') - 'align' || jsonb('{"align": "center"}'), true), '{}' ) ) 
                  else
                     arr 
               end
) as new_values 
            from
               jsonb_array_elements( entities ) as arr 
               group by
                  arr -> 'content' ->> 'align',
                  arr 
         )
         as new_values 
   )
;

Related Solutions

Postgresql – Combining Postgres jsonb query statements

What I ended up doing using inspiration from MatheusOl answer.

INSERT INTO test (post_id, username, votes) 
VALUES (12345, 'testuser', '{"commentid" : {"vote": true }}'::jsonb) 
  ON CONFLICT ON CONSTRAINT test_pkey DO UPDATE

  SET votes = test.votes || coalesce(test.votes->'commentid', '{"commentid" : {"vote: true"}}'))
   || jsonb_set(test.votes, '{commentid,vote}', 'true'::jsonb) 

WHERE NOT test.votes @> EXCLUDED.votes
RETURNING *;

When using his answer, "commentid" would still be overwritten, but I don't really understand why.

This doesn't do things very efficiently, as it recreates the "commentid" key every time as a new object, but in my case it will have significantly more reads than writes, so it should be fine

Postgresql index for jsonb array field

The index is only used when Postgres expects it to help performance - which is only the case if the filter is expected to be selective enough (~ 5 % or less of the rows qualify, percentage heavily depends on various details).

One problem with document types like jsonb: Postgres currently does not maintain statistics about value frequencies of embedded elements (still true in Postgres 10). So it has to base its decision whether or not to use an index on generic frequency estimations. Meaning, even if your particular filter data -> 'school_id' ->> 1 IS NOT NULL is very selective and using the index would pay, Postgres works with a generic average estimation and might miss the opportunity.

There are ways around this with expression or partial indexes, because Postgres collects separate statistics for index expressions. The best you could do for this particular query would be a partial index:

CREATE INDEX data_school_id ON events (event_id)  -- idx column largely irrelevant here
WHERE (data -> 'school_id' ->> 1) IS NOT NULL;

And VACUUM ANALYZE events; at least once (or wait until autovacuum kicks in).

Best Answer

Related Solutions

Postgresql – Combining Postgres jsonb query statements

Postgresql index for jsonb array field

Related Question