CODEWITHSUNDEEP
postgresqljsonbpostgresql-json
Jitendra
Jitendra

Reputation: 604

Postgresql get keys from array of objects in JSONB field

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

Upvotes: 6

Views: 9883

Answers (2)

S-Man
S-Man

Reputation: 23666

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.

Upvotes: 9

shuba.ivan
shuba.ivan

Reputation: 4051

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}';

Upvotes: 0

Related Questions