PSQL - Query to transpose JSONB element

Question

I have a jsonb object like this:

{
  "applicants": [
    {
      "last_name": "ss",
      "first_name": "ss",
      "age": 31
    },
    {
      "last_name": "kk",
      "first_name": "kk",
      "age": 32
    }
  ]
}

I want to convert it to.

{
  "applicants": [
    {
      "last_name": "ss",
      "data": {            
        "first_name": "ss",
        "age": 31
      }
    },
    {
      "last_name": "kk",
      "data": {            
        "first_name": "kk",
        "age": 32
      }
    }
  ]
}

I have done a similar thing using jsonb_array_elements and jsonb_build_object before, but I can't figure out how I would create a new data object inside each object, and transpose the fields into it.

Is it possible to write this in plain psql query?

Thanks.

Answer 1

I have to point out that Postgres is not the best tool for modifying JSON data structures, and if you feel the need to do so, it probably means that your solution is in general not optimal. While Postgres has the necessary features to do this, I wouldn't want to maintain code that contains queries like the following.

update my_table set
json_col = (
    select 
        jsonb_build_object(
            'applicants',
            jsonb_agg(
                elem- 'first_name'- 'age' || jsonb_build_object(
                    'data', 
                    jsonb_build_object(
                        'first_name', 
                        elem->'first_name', 
                        'age', 
                        elem->'age'
                    )
                )
            )
        )
    from jsonb_array_elements(json_col->'applicants') as arr(elem)
    )

Test it in db<>fiddle.