Postgresql and jsonb - inserting a key/value into a multi-level array

Question

Very similar to this post, but I struggle to adapt from their solution..

My table : public.challenge, column lines JSONB

My initial JSON in lines :

[
  {
    "line": 1,
    "blocs": [
      {
        "size": 100,
        "name": "abc"
      },
      {
        "size": 100,
        "name": "def"
      },
      {
        "size": 100,
        "name": "ghi"
      }
    ]
  },
  {
    "line": 2,
    "blocs": [
      {
        "size": 100,
        "name": "xyz"
      }
    ]
  }
]

Desired update :

[
  {
    "line": 1,
    "blocs": [
      {
        "size": 100,
        "name": "abc",
        "type": "regular"
      },
      {
        "size": 100,
        "name": "def",
        "type": "regular"
      },
      {
        "size": 100,
        "name": "ghi",
        "type": "regular"
      }
    ]
  },
  {
    "line": 2,
    "blocs": [
      {
        "size": 100,
        "name": "xyz",
        "type": "regular"
      }
    ]
  }
]

So basically I need to add the type key+value in every object of blocs, for each element of the root array.

My unsuccessful attempt looks like this :

UPDATE public.challenge SET lines = jsonb_set(lines, '{}', (

    SELECT jsonb_set(line, '{blocs}', (
        SELECT jsonb_agg( bloc || '{"type":"regular"}' )
        FROM jsonb_array_elements(line->'{blocs}') bloc
    ))

    FROM jsonb_array_elements(lines) line
))
;

(currently it sets the whole column as null, maybe due to jsonb_set(lines, '{}' while my json begins as an array ?)

Thanks!

Barbaros Özhan · Accepted Answer · 2021-06-16 07:23:31Z

2

Use jsonb_array_elements to unnest all the array elements and then add the required json and use jsonb_agg to aggregate it again:

with cte as
 (select id,
         jsonb_agg(jsonb_set(val1,
                             '{blocs}',
                             (select jsonb_agg(arr2 || '{"type": "regular"}')
                                from jsonb_array_elements(arr1.val1 - >
                                                          'blocs') arr2)))
    from challenge, 
         jsonb_array_elements(lines) arr1(val1)
   group by 1) 
update challenge 
   set lines = (cte.jsonb_agg) 
  from cte 
 where challenge.id = cte.id

DEMO

edited Jun 16, 2021 at 7:23

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

answered Jun 15, 2021 at 17:07

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

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

1 Comment

lgean Over a year ago

Great thanks! By any chance, I'd like the value to be arr2->'oldType' (instead of always "regular"), how would you concatenate this in the jsonb_agg function ?

Collectives™ on Stack Overflow

Postgresql and jsonb - inserting a key/value into a multi-level array

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related