Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

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')
FROM 
    data,
    jsonb_array_elements(jsondata) AS elems

jsonb_agg

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

SELECT 1

WITH data AS (
    SELECT '[ { "name": [ "sun11", "sun12" ], "alignment": "center", "more": "fields" }, { "name": [ "sun12", "sun13" ], "alignment": "center" }, { "name": [ "sun14", "sun15" ] }]'::jsonb AS jsondata
)
SELECT 
    *
FROM 
    data

jsondata

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

SELECT 1

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

value

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

{"name": ["sun12", "sun13"], "alignment": "center"}

{"name": ["sun14", "sun15"]}

SELECT 3

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

?column?

["sun11", "sun12"]

["sun12", "sun13"]

["sun14", "sun15"]

SELECT 3