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

Sarinasarine

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.

Kenay

Can you please explain me how does this works elems.value -> 'name'? or what does elems.vlaue means? – Sarinasarine

jsonb_array_elements creates a new column named "value". In this row all array elements have been inserted. You can select them by elems.value (see fiddle). Now every of these elements can be asked for the value of the key "name". – Kenay

Answer 2

my example

SELECT DISTINCT sub.name FROM (

SELECT
jsonb_build_object('name', p.data->'name') AS name
FROM user AS u
WHERE u.data IS NOT NULL
) sub
WHERE sub.name != '{"name": null}';

Veneaux