Postgres 12

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 autos AS (
    SELECT '{"cars": [{"name": "BMW", "year": 1991}, {"name": "Ford", "model": "Mustang"}, {"name": "Fiat"}, {"name": "VW", "model": "Golf", "year": 2000}, {"name": "VW", "model": ""}]}'::jsonb as data
)
SELECT jsonb_pretty(data) FROM autos 


jsonb_pretty


{
    "cars": [
        {
            "name": "BMW",
            "year": 1991
        },
        {
            "name": "Ford",
            "model": "Mustang"
        },
        {
            "name": "Fiat"
        },
        {
            "name": "VW",
            "year": 2000,
            "model": "Golf"
        },
        {
            "name": "VW",
            "model": ""
        }
    ]
}


WITH autos AS (
    SELECT '{"cars": [{"name": "BMW", "year": 1991}, {"name": "Ford", "model": "Mustang"}, {"name": "Fiat"}, {"name": "VW", "model": "Golf", "year": 2000}, {"name": "VW", "model": ""}]}'::jsonb as data
)
SELECT 
    car ->> 'name' as name,
    car ->> 'year' as year,
    car ->> 'model' as model
FROM autos,
    jsonb_array_elements(data->'cars') as car


name
year
model


BMW
1991
null

Ford
null
Mustang

Fiat
null
null

VW
2000
Golf

VW
null



WITH autos AS (
    SELECT '{"cars": [{"name": "BMW", "year": 1991}, {"name": "Ford", "model": "Mustang"}, {"name": "Fiat"}, {"name": "VW", "model": "Golf", "year": 2000}, {"name": "VW", "model": ""}]}'::jsonb as data
)
SELECT 
    NULLIF(car ->> 'name', '') as name,
    NULLIF(car ->> 'year', '') as year,
    NULLIF(car ->> 'model', '') as model
FROM autos,
    jsonb_array_elements(data->'cars') as car


name
year
model


BMW
1991
null

Ford
null
Mustang

Fiat
null
null

VW
2000
Golf

VW
null
null


WITH autos AS (
    SELECT '{"cars": [{"name": "BMW", "year": 1991}, {"name": "Ford", "model": "Mustang"}, {"name": "Fiat"}, {"name": "VW", "model": "Golf", "year": 2000}, {"name": "VW", "model": ""}]}'::jsonb as data
)
SELECT 
    STRING_AGG(NULLIF(car ->> 'name', ''), ',') as names,
    STRING_AGG(NULLIF(car ->> 'year', ''), ',') as years,
    STRING_AGG(NULLIF(car ->> 'model', ''), ',') as models
FROM autos,
    jsonb_array_elements(data->'cars') as car


names
years
models


BMW,Ford,Fiat,VW,VW
1991,2000
Mustang,Golf


WITH autos AS (
    SELECT '{"cars": [{"name": "BMW", "year": 1991}, {"name": "Ford", "model": "Mustang"}, {"name": "Fiat"}, {"name": "VW", "model": "Golf", "year": 2000}, {"name": "VW", "model": ""}]}'::jsonb as data
)
SELECT 
    'Cars: ' || STRING_AGG(NULLIF(car ->> 'name', ''), ',') || ' // '
    'Years: ' || STRING_AGG(NULLIF(car ->> 'year', ''), ',') || ' // '
    'Models: ' || STRING_AGG(NULLIF(car ->> 'model', ''), ',')
FROM autos,
    jsonb_array_elements(data->'cars') as car


?column?


Cars: BMW,Ford,Fiat,VW,VW // Years: 1991,2000 // Models: Mustang,Golf
name	year	model
BMW	1991	null
Ford	null	Mustang
Fiat	null	null
VW	2000	Golf
VW	null