6

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

Jitendra
  • 584
  • 1
  • 10
  • 28

2 Answers2

8

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.
Raz Luvaton
  • 3,166
  • 4
  • 21
  • 36
S-Man
  • 22,521
  • 7
  • 40
  • 63
  • Can you please explain me how does this works `elems.value -> 'name'`? or what does elems.vlaue means? – Jitendra May 07 '19 at 07:14
  • 1
    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". – S-Man May 07 '19 at 07:20
0

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}';
shuba.ivan
  • 3,824
  • 8
  • 49
  • 121