I have a jsonb object like this:

{
  "members": [
    [
      "1966-07-31",
      null,
      {
        "last_name": "ss",
        "first_name": "ss"
      }
    ],
    [
      "1968-12-17",
      "spouse",
      {
        "last_name": "kk",
        "first_name": "kk"
      }
    ]
  ]
}

I want to convert it to.

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

Essentially taking the third element of each member and putting is an object in a new array 'applicant'. I don't need the member data that is outside the object.

I can run a PHP script to loop through all the rows and update it. But I'm wondering if I can write this in plain sql query?

Thanks.

CodePudding user response：

You should use jsonb_array_elements with two CROSS JOIN for extract JSON array data then aggregate them

Dynamic array:

Demo

select
  jsonb_build_object('applicants', jsonb_agg(e2.value))
from
  test t
  cross join jsonb_array_elements(t.obj -> 'members') as e1
  cross join jsonb_array_elements(e1.value) as e2
where 
  e2.value ? 'first_name'
  and e2.value ? 'last_name'

Static array:

If your structure is specific you don't need a loop over the array and can use the below query:

Demo

select
  jsonb_build_object('applicants', jsonb_agg(e1.value -> 2))
from
  test t
  cross join jsonb_array_elements(t.obj -> 'members') as e1