Postgresql get keys from array of objects in JSONB field

Question

Here' a dummy data for the jsonb column

[ { "name": [ "sun11", "sun12" ], "alignment": "center", "more": "fields" }, { "name": [ "sun12", "sun13" ], "alignment": "center" }, { "name": [ "sun14", "sun15" ] }]

I want to fetch all the name keys value from jsonb array of objects...expecting output -

[ [ "sun11", "sun12" ], [ "sun12", "sun13" ], [ "sun14", "sun15" ] ]

The problem is that I'm able to fetch the name key value by giving the index like 0, 1, etc

SELECT data->0->'name' FROM public."user";
[ "sun11", "sun12" ]

But I'm not able to get all the name keys values from same array of object.I Just want to get all the keys values from the array of json object. Any help will be helpful. Thanks

Answer 1

demo:db<>fiddle (Final query first, intermediate steps below)

WITH data AS (
    SELECT '[ { "name": [ "sun11", "sun12" ], "alignment": "center", "more": "fields" }, { "name": [ "sun12", "sun13" ], "alignment": "center" }, { "name": [ "sun14", "sun15" ] }]'::jsonb AS jsondata
)
SELECT 
    jsonb_agg(elems.value -> 'name')    -- 2
FROM 
    data,
    jsonb_array_elements(jsondata) AS elems -- 1

1. jsonb_array_elements() expands every array element into one row
2. -> operator gives the array for attribute name; after that jsonb_agg() puts all extracted arrays into one again.