CODEWITHSUNDEEP
sqlpostgresqlsql-updatejsonb
lgean
lgean

Reputation: 295

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

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!

Upvotes: 2

Views: 383

Answers (1)

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6130

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

Upvotes: 2

Related Questions